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

#### pips30

##### New Member
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....

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Jonmo1

##### MrExcel MVP
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...
Code:
``{=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

##### New Member
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...
Code:
``{=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

##### MrExcel MVP
And it's confirmed with CTRL + SHIFT + ENTER ???

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

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

#### pips30

##### New Member
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)

Replies
1
Views
284
Replies
12
Views
444
Replies
7
Views
201
Replies
21
Views
652
Replies
10
Views
226

1,191,073
Messages
5,984,473
Members
439,891
Latest member
maikii

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back