How to sum values based on multiple criteria with multple possibilities for each criteria?

Hi,

I encounter some problem when trying to sum based on multiple criteria. My data is something like this

Cat Sherry 10
Cat Ben 5
Cat Ken 2
Dog Sherry 2
Dog Ben 1
Dog Ken 6
Fish Sherry 15
Fish Ben 1
Fish Ken 7

I have 2 criteria: Animal and owner
What I hope to do is to sum up all the values in the 3rd column that belongs to Cat and Dog under animal and Sherry and Ben under owner. So I will like to get the sum of 18 (10+5+2+1) Is there a way to do this with a formula?

Thank you in advance for any help given!

Please see attached file with formulas.

Originally Posted by boredaxel

What I hope to do is to sum up all the values in the 3rd column that belongs to Cat and Dog under animal and Sherry and Ben under owner.

Try this formula

=SUM(SUMIFS(C:C,A:A,{"cat","dog"},B:B,{"sherry";"ben"}))

Modify formula from daddylonglegs it's array formula so don't forget press ctrl+shift+enter

....but that gives a result of 11 - isn't the answer 18? (sum of C2, C3, C5 and C6)

I think my formula gives that answer

=SUM(SUMIFS(C:C,A:A,{"cat","dog"},B:B,{"sherry";"ben"}))

[doesn't need array entry]

Note that in {"cat","dog"} there is a comma separator and in {"sherry";"ben"} there is a semi-colon. It has to be written that way (or the first with semi-colon the second with comma)

If you have the criteria in columns as shown in SDCh's sheet then you need to use TRANSPOSE on one of them, i.e.

=SUM(SUMIFS(C2:C15,A2:A15,F2:F5,B2:B15,TRANSPOSE(G2:G5)))

confirmed with CTRL+SHIFT+ENTER

....or a non-array version like this will be more "extensible", e.g. if you want more multi-option criteria

=SUMPRODUCT(ISNUMBER(MATCH(A2:A15,F2:F5,0))*ISNUMBER(MATCH(B2:B15,G2:G5,0)),C2:C15)

Hi daddylonglegs,

You are right, it does give 18. Sorry for my mistake. I tried your formula by replacing ,{"cat","dog"}, and ,{"sherry";"ben"} with the cells containing the criteria e.g. (e2:e3) and (f2:f3). That gave me a sum of 11 instead of 18. What you suggested with the transpose works! Thanks!!

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.