sumifs with multiple criteria in one column.

Say ColA is the criteria I want to check against and ColB is the column I want to sum:

If you have only 1 Criteria then it's easy: =SUMIFS(Table1[ColB],Table1[ColA],"DOG")

But how do you do it if you want to sum ColB if ColA is either "DOG" or "CAT"?

Seems easy conceptually....(and I guess you could use SUMPRODUCT but want the efficiency of the *IFS functions) but can't figure it out googling....which tells me I'm really missing something.

Is it possible?

Many thanks,
Al

Hello Al, try like this

=SUM(SUMIFS(Table1[ColB],Table1[ColA],{"DOG","CAT"}))

in that case, use SUMPRODUCT instead of the outermost SUM function.

=SUMPRODUCT(SUMIFS(Table1[ColB],Table1[ColA],S1:S2))

seemingly, SUMIFS is several times more efficient than SUMPRODUCT. but, if you have very few criteria_range cells (S1, S2 in this case), then, i guess, it may not really matter which one you choose. if you had 10-15 cells for that range, would you type that many SUMIFS, instead of a very simple SUMPRODUCT construct? also, if this is a formula that is only going to reside in a single cell (and not be dragged over a range), performance might not be perceptibly different between the two. if you had to drag that formula over several 100s or 1000s of rows, i would, without hesitation, choose SUMIFS.