Array Formulas Not Working...And (I'm sure) It Should!!!

[pips30]

I am using array formulas to currently sum the amounts of data depending on certain criteria.
Here's an example of one that is working:

{=SUM(((Data!$I$2:$I$2000="Y")*(Data!$K$2:$K$2000="R")*(Data!$L$2:$L$2000="SLCA")))}

Okay, so now that there are more variables in column L, I need to expand the formula to incorporate them. The first 2 conditions remain the same but the last one is now: "IS"
Logic would dictate that this should work:

{=SUM(((Data!$I$2:$I$2000="Y")*(Data!$K$2:$K$2000="E")*(Data!$L$2:$L$2000="IS")))}

It doesn't!!!!????

From filtering I know that the result should be 4 - the result being pulled back is 0.

Try as I might I cannot figure out why?
Anyone got any ideas....

 

[Jonmo1]

Are you SUMMING - Or COUNTING ???

If you are SUMMING, you haven't given it a range to sum, you've only specified criteria...

If you are COUNTING, then yes the formula should work.

1. Make sure you're confirming with CTRL + SHIFT + ENTER

2. Check for EXACT spelling, no EXTRA Spaces...

Try this to check for extra spaces...

{=SUM(((TRIM(Data!$I$2:$I$2000)="Y")*(TRIM(Data!$K$2:$K$2000)="E")*(TRIM(Data!$L$2:$L$2000)="IS")))}

Also, I hope this was a typo, but you said the First 2 criteria should remain the same, only the 3rd criteria changed. However, your first example formula used "R" as the 2nd criteria, But your 2nd formula uses "E" as the 2nd criteria.

 

[pips30]

Are you SUMMING - Or COUNTING ???

If you are SUMMING, you haven't given it a range to sum, you've only specified criteria...

If you are COUNTING, then yes the formula should work.

1. Make sure you're confirming with CTRL + SHIFT + ENTER

2. Check for EXACT spelling, no EXTRA Spaces...

Try this to check for extra spaces...

{=SUM(((TRIM(Data!$I$2:$I$2000)="Y")*(TRIM(Data!$K$2:$K$2000)="E")*(TRIM(Data!$L$2:$L$2000)="IS")))}

Also, I hope this was a typo, but you said the First 2 criteria should remain the same, only the 3rd criteria changed. However, your first example formula used "R" as the 2nd criteria, But your 2nd formula uses "E" as the 2nd criteria.

Yea - that was definitely a typo; think I need to take lunch!
Yes - I am counting not summing; that's why I can't figure out why it doesn't work.

Thanks for that TRIM formula; it did uncover spaces, alas, still not the correct results...

Just call me baffled

 

[Jonmo1]

And it's confirmed with CTRL + SHIFT + ENTER ???

Try using sumproduct, instead of the array formula so you don't have to do that....

=SUMPRODUCT((Data!$I$2:$I$2000="Y")*(Data!$K$2:$K$2000="R")*(Data!$L$2:$L$2000="IS"))

 

[pips30]

And it's confirmed with CTRL + SHIFT + ENTER ???

Try using sumproduct, instead of the array formula so you don't have to do that....

Bingo! That seemed to do the trick...thanks v much
(it'll still bug me why the array didn't work - just one of life's little mysteries)