Multi-Conditional SUMIF

  • Thread starter Thread starter JCH
  • Start date Start date
J

JCH

OK, I have scoured through the newsgroups, forum sites and
haven't been able to track anything down that helps me
with the following problem.

Col1 Col2 Col3
yes CDN 15
no CDN 20
yes US 20
yes CDN 20
no US 25
yes US 15

I need to sum Column 3 for each scenario (there will only
ever be four):
YES|CDN
NO |CDN
YES|US
NO |US

I've tried the following function, but it simply returns
#VALUE!:
=SUM(IF((A2:A41="Yes") * (B2:B41="CDN"),SUM(C2:C41),0))

Any and all help would be greatly appreciated.
Cheers,
JCH
 
JCH, I am glad to see there are people who give it an honest go before
asking here! Your SUM formula isn't TOO far off, but you'd have to enter as
an array formula, and the IF is unneccessary (among other things), be that
as it may..

This can be worked with SUMPRODUCT like this:

=SUMPRODUCT((A1:A10="Yes")*(B1:B10="CDN"),C1:C10)
 
Thanks for your help Dave. I actually kept hunting on
the "cheat", and found the Conditional Sum Wizard. It did
it in a snap, but my lord the syntax was horrible. Dollar
signs everywhere.

I redid it with the =SUMPRODUCT array function and it is
100% cleaner and its easier to explain to someone who
needs to troubleshoot in the future. The Excel Help does a
poor job explaining this function (lacking a real life or
variety of examples).

Thanks again.
JCH
 
Back
Top