# Array or SumIf

#### JontyHart

##### New Member
I'm attempting to sum numbers based on the values in my first two columns. For instance, if column A=East and Column B=Hats, add the values in column C, in this case 100.

East Hats 100
West Shoes 20
North Hats 30
South Shoes 40
East Shoes 50

I thought I could use an array formula with an If and an AND in it.
This is the formula I attempted, which didn't work.

SUM(IF(AND(\$A\$4:\$A\$8="East",\$B\$4:\$B\$8="Hats"),\$C\$4:\$C\$8))

Any suggestions?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi JontyHart

Try:

=SUMPRODUCT(--(\$A\$4:\$A\$8="East"),--(\$B\$4:\$B\$8="Hats"),\$C\$4:\$C\$8)

Hope this helps
PGC

=SUM(IF(\$A\$4:\$A\$8="East",IF(\$B\$4:\$B\$8="Hats",\$C\$4:\$C\$8)))

which needs to be confirmed with control+shift+enter, not just with enter.

Or, alternatively...

=SUMPRODUCT(--(\$A\$4:\$A\$8="East"),--(\$B\$4:\$B\$8="Hats"),\$C\$4:\$C\$8)

which just needs enter.

Thank you so much!!

I understand the logic for the array formula with the SUM(If) version that you provided, but can you explain to me the reason for the dashes in the SUMPRODUCT formula. I see that they are imperative or the formula doesn't calculate properly, but I'm just not sure I understand why they are required.

Thank you so much!!

I understand the logic for the array formula with the SUM(If) version that you provided, but can you explain to me the reason for the dashes in the SUMPRODUCT formula. I see that they are imperative or the formula doesn't calculate properly, but I'm just not sure I understand why they are required.

See:

http://www.mrexcel.com/board2/viewtopic.php?t=133602

for an attempt.

Thanks again!

This board is so interesting and I'm learning so much!

Replies
0
Views
77
Replies
2
Views
197
Replies
0
Views
434
Replies
6
Views
544
Replies
5
Views
406

1,206,811
Messages
6,075,002
Members
446,113
Latest member

### 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