# Sumif formula on an array of criteria?

1. ## Sumif formula on an array of criteria?

I am curious if there is a formula that will return the sum of all values that meet multiple criteria. I have attached an example and while I know that there are a couple of options using sumif() formulas with a single critera, I am looking for a solution where I can have an array or mutliple critera to return the sum of. The example attached should give you the general idea but my real life example has much more data so I could nont simpily string 5 sumif formulas together to return me desired outcome. Let me know if you have any suggestions or need further explaination of what I looking for.

Thank you,  Register To Reply

2. ## Re: Sumif formula on an array of criteria?

For 2007+ use =SUMIFS(sum_range,criteria_range,criteria,...)  Register To Reply

3. ## Re: Sumif formula on an array of criteria?

This formula will let you sum the quantity for a range of criteria

=SUMPRODUCT(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17))  Register To Reply

4. ## Re: Sumif formula on an array of criteria?

DaddyL, I don't think yours works for what I think Dail1 wants (?).
But if you change it from =SUMPRODUCT(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17))
to =SUM(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17)) I think that'll work (again, unless I'm misunderstanding the request).   Register To Reply

5. ## Re: Sumif formula on an array of criteria?

Sum array example2.xlsxSum array example2.xlsx Originally Posted by jeffreybrown For 2007+ use =SUMIFS(sum_range,criteria_range,criteria,...)
Thanks for the heads up on the old profile info.

I have been playing around with sumifs() but I can't get if to work properly. Please see the new attachment and let me know if I am entering the correct formula. Essentially I would like the formula in this example to = the sum of all the numbers in wk1.  Register To Reply

6. ## Re: Sumif formula on an array of criteria?

Hi Dial1,

If you look at post #3, put that formula in B8 and it returns the total of 493. Not sure why you couldn't just use, =SUM(B3:B7)

Also, in B2 you did not apply the absolutes to the formula completly...

=SUMIF(Qty!\$A\$3:\$A\$17,Summary!\$A3,Qty!\$B\$3:\$B\$17)  Register To Reply

7. ## Re: Sumif formula on an array of criteria? Originally Posted by Sambo kid DaddyL, I don't think yours works for what I think Dail1 wants (?).
But if you change it from =SUMPRODUCT(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17))
to =SUM(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17)) I think that'll work (again, unless I'm misunderstanding the request). Hey Sambo kid! Isn't that the same thing except SUM instead of SUMPRODUCT? The two formulas will do the same thing, I only used SUMPRODUCT because it avoids "array entry" Originally Posted by jeffreybrown Not sure why you couldn't just use, =SUM(B3:B7)
Exactly Jeff! That what I was thinking......  Register To Reply

8. ## Re: Sumif formula on an array of criteria?

Don't want to argue with someone who I think (scratch that, KNOW) has a lot more expertise than I have but the only reason I questioned it was because I tried yours on Dial1's data and got very different results than I got when I put sum in place of sumproduct.   Register To Reply

9. ## Re: Sumif formula on an array of criteria? Originally Posted by Sambo kid Don't want to argue with someone......
No problem - I like a good argument..... I got 493 using SUMPRODUCT. If you use SUM and array enter the formula I think you should get the same thing, both are valid approaches  Register To Reply

10. ## Re: Sumif formula on an array of criteria?

This is where I had a different idea of what Dial1 wanted. When I used sumproduct I got 493 for week one for A (too). But I assumed Dial1 wanted the sum of A's for week 1 to be 22 which is what I got if I went down week 1 and added the #s for A together. So using sum got me 22 but sumproduct got 493.
So that is why I recommended Dial1 tweek your formula. Just trying to clarify.   Register To Reply

11. ## Re: Sumif formula on an array of criteria?

OK, yes we are talking at cross purposes.

My intention with the formula I posted, i.e.

=SUMPRODUCT(SUMIF(Qty!\$A3:\$A17,Summary!A3:A7,Qty!B3:B17))

...was the formula to be entered just once to get the quantities for all 5 criteria added together, "A", "B", "C", "D" and "E"

If you use the same formula with SUM in place of SUMPRODUCT (without array entering) then the formula actually returns an array of values but the value you see displayed depends on where you enter it. If you enter in B3 and copy down you get the 5 quantities in B3:B7. If that's the requirement then it would be better to use the simple SUMIF that Jeff suggest in post #6  Register To Reply

12. ## Re: Sumif formula on an array of criteria?

Thanks for the explanation, maybe my reading comprehension isn't what it used to be. But I saved the formulas you put in for my (possible) future use.   Register To Reply

13. ## Re: Sumif formula on an array of criteria?

Thank you all. Worked like a charm!!  Register To Reply