# sumifs with multiple criteria in one column.

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

2. ## Re: sumifs with multiple criteria in one column.

Hello Al, try like this

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

3. ## Re: sumifs with multiple criteria in one column.

Wow, that works.
That is so not obvious from reading the Excel docs and books I've looked at so far.
Thanks so much!
-Al

Originally Posted by daddylonglegs
Hello Al, try like this

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

4. ## Re: sumifs with multiple criteria in one column.

Bit of a thread dig

But say Dog, Cat were in a cell reference (let's say cells S1, S2), is there a way to use this formula but to link to a reference?

5. ## Re: sumifs with multiple criteria in one column.

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

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

6. ## Re: sumifs with multiple criteria in one column.

Originally Posted by icestationzbra
in that case, use SUMPRODUCT instead of the outermost SUM function.

=SUMPRODUCT(SUMIFS(Table1[ColB],Table1[ColA],S1:S2))
Thanks - is this more or less efficient than using multiple SUMIFS? i.e. =SUMIFS(Table1[ColB],Table1[ColA],S1)+SUMIFS(Table1[ColB],Table1[ColA],S2)

7. ## Re: sumifs with multiple criteria in one column.

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.

8. ## Re: sumifs with multiple criteria in one column.

Originally Posted by daddylonglegs
Hello Al, try like this

=SUM(SUMIFS(Table1[ColB],Table1[ColA],{"DOG","CAT"}))
HI, it seems to be an ols thread but i was searching for this issue,
i have a question, do i have to put SUM before SUMIFS!!?

##### Users Browsing this Thread

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