Array or SumIf

JontyHart

New Member
Joined
Aug 23, 2006
Messages
6
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi JontyHart

Try:

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

Hope this helps
PGC
 
Upvote 0
=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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,374
Members
453,655
Latest member
lasvegasbuffet

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top