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

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

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

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

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).

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.

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)

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

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.

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

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.

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

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.

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

Thank you all. Worked like a charm!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1