• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumifs or sumproduct? If - and - sum calculation

B

Burl

New Member
pk oz qty
12 16 300
6 16 1300
4 64 400
6 9 800
12 10 300
6 19 520
4 128 28
12 16 100
6 6 150

What I'm trying to do is use two conditions - that if true will sum column c.
For instance - if column a = 12 AND column b = 16 then sum column c.

I came up with the formula below based on previous posts. But this formula sums the entire column c (for an answer of 3698) - when the answer should be 400.
Maybe I should be using something other than sumproduct?
=SUMPRODUCT(($c$c2:$c$10)-($a$2:$a$10="12")-($b$2:$b$10="16"))

This IF AND formula pulls the data I want - but doesn't total the column:
=IF(AND(a2=12,b2=16),c2)

This SumIF formula only works with one argument and not two:
=SUMIF(a2:a10,12,c2:c10)

Is there any way to have two arguments AND sum an entire column?
 
Your SUMPRODUCT formula is a little off in structure. Need to multiply the arrays against each other, not subtract them. Should have been:
=SUMPRODUCT(($c$c2:$c$10)*($a$2:$a$10=12)*($b$2:$b$10=16))

Also, note that I removed the quotation marks from the numbers. Things with quotation marks are treated as text, and XL won't compare them properly with actual numbers.

The IF function could work, but we'd have to make it an array, and it's not really built for this.

If you have XL 2007+, your best choice is the SUMIFS function. Formula would be:
=SUMIFS(C2:C10,A2:A10,12,B2:B10,16)
 
You must log in or register to reply here.
Back
Top