Adding to a SUMIF Function 1

[Mizzness]

All.

I am trying to do is add the IF(AND function to my SUMIF formula.
What I am trying to accomplish is have the formula locate Account 1614 in Col.A & the currency "EUR" in Col.M and then use the SUMIF formula to give me my answer.

My formula is as follows:
=IF(AND('LDN Data'!M:M=EUR,'LDN Data'!A:A=1614),SUMIF('LDN Data'!A:A,sbl!$C33,'LDN Data'!S:S))

Column "M" is populated with various currencies & column "A" is populated with various account numbers.

Thanx for any future guidance.
Inthe SUMIF formula, columns "A" & "S" are constant.

 

[AnotherHiggins]

What is in cell [green]'sbl'!$C$33[/green]?

You are already checking to see if 'LDN Data'!A:A=1614, and then you check to see if 'LDN Data'!A:A = 'sbl'!$C$33 in your sumif.

Assuming that 'sbl'!$C$33 is 1614, then try this:
[tab][COLOR=blue white]=sumproduct(--('LDN Data'!M:M="EUR") * --('LDN Data'!A:A=1614) * --('LDN Data'!S:S))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

John,

Cell 'sbl'!$C$33 is the Account Number in the summary where the formula resides. You are correct in your assumption.

 

[Mizzness]

I have tried your suggestion but am getting an error.

This is what I used:
=SUMPRODUCT(--('LDN Data'!A:A=1614)*--('LDN Data'!M:M=EUR)*--('LDN Data'!S:S))

 

[AnotherHiggins]

Enclose EUR in quotes.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

Getting #NUM error now.

 

[AnotherHiggins]

Sorry! I forgot one of the first rules of SumProduct: It cannot be used on an entire column.

Try this instead:
[COLOR=blue white]=SUMPRODUCT(--('LDN Data'!M2:M1000="EUR") * --('LDN Data'!A2:A1000=1614) * --('LDN Data'!S2:S1000))[/color]

You can change from 1000 to whatever row you want - but make sure that each section of the formula contains the same number of cells. (i.e. you can't have M2:M1000="EUR" and A2:A5000=1614)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

We're getting there. My answer though is now zero.

The formula is as follows :
=SUMPRODUCT(--('LDN Data'!A2:A1000=1614)*--('LDN Data'!M2:M1000="EUR")*--('LDN Data'!S2:S1000))

 

[AnotherHiggins]

Check to make sure that column A is formatted as numbers. As a test, type this formula into a new column:
=IsNumber(A2)
(Change A2 to a cell containing 1614)

Does that return TRUE or FALSE?

Assuming it returns false, change the formula to include quotes around the 1614 like so:
=SUMPRODUCT(--('LDN Data'!A2:A1000=[red]"[/red]1614[red]"[/red]) * --('LDN Data'!M2:M1000="EUR") * --('LDN Data'!S2:S1000))


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

Column "A" is formatted to text. I had previously tried the quotes in the formula but to no avail.

 

[AnotherHiggins]

Just to humor me, please copy and paste the formula from my last post exactly. Because that should work.

If not, perhaps we should back up and make sure I understand what you're after....

That formula will do the following (if this isn't what you want, please restate the logic you are after):
-Sum the values in column S only where:
[tab]-Column A is 1614
[tab]AND
[tab]-Column M is EUR

If you are getting zero, are you sure there are rows where both conditions are met and there is a non-zero value in column S?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

BTW, it works for me with "1614"

Skip,

_[red][/red]

 

[Mizzness]

John,

1. You do understand what I am after. I also don't understand why it doesn't work.
2. The is no non-zero value in column S
3. There are definitely rows where both conditions are met.

Even after I pasted your formula I get zero.
=SUMPRODUCT(--('LDN Data'!A2:A1000="1614") * --('LDN Data'!M2:M1000="EUR") * --('LDN Data'!S2:S1000))

 

[AnotherHiggins]

2. The[red][re?][/red] is no non-zero value in column S

Could you clarify that? I think you just said that all values in column S are zeros. If that is the case, then obviously the result will be zero.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

No, my mistake.
That column has zeros as well as values.

However, all figures for the logic I want do not contain any zero value.
Btw, I also checked that column using ISNumber & the data is formatted as numbers.

 

[KenWright]

Your syntax in the function is incorrect. It is either

SUMPRODUCT(Range1*Range2*Range3)

or

SUMPRODUCT(--(Range1),--(Range2),--(Range3))

The second form will usually get you round the issue of having text or spaces in your data if you are making calcs on it (ie when the header is included in the ranges and the ranges are numeric).

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Are you sure that you have not got extra spaces in the cells "EUR " rather than "EUR" for example?


Gavin

 

[Mizzness]

Ken,

Thank you for the clarification on the syntax.

John,
Thanks for your patience.

The formula is as follows:
=SUMPRODUCT(--('LDN DATA'!A2:A1000=1614), --('LDN DATA'!M2:M1000="EUR"), --('LDN DATA'!S2:S1000))

This is presuming columns A & M are in text form.

 

[KenWright]

Try building it in bits and see what bits don't work:-

In your sheet 'LDN DATA':-

- In cell Z1 put "1614" in the same format as in your col A.
- In cell Z2 put "EUR".
- In cell V2 put =--(A2=$Z$1)
- In cell W2 put =--(M2=$Z$2)
- In cell X2 put =V2*W2

Copy V2:X2 down to V1000:X1000

Do you see any 1s against the values that you believe match your criteria?
Do you see any 1s in column X?

If yopu don't see any 1s in columns V or W then the data does not match, regardless of what you think you are seeing.

If you see the 1s in col X but the formula does not return the same data, then try replacing the hardwired values in the formula with references to Z1 and Z2 and see if that makes a difference (It should if you are seeing 1s in col X)

Regards
Ken............

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------