+ Reply to Thread
Results 1 to 8 of 8

sumifs with multiple criteria in one column.

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    Question 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
    Last edited by apolloni; 07-01-2010 at 03:59 AM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumifs with multiple criteria in one column.

    Hello Al, try like this

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

  3. #3
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    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

    Quote Originally Posted by daddylonglegs View Post
    Hello Al, try like this

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

  4. #4
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    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. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    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))
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  6. #6
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: sumifs with multiple criteria in one column.

    Quote Originally Posted by icestationzbra View Post
    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. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    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. #8
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    16

    Thumbs up Re: sumifs with multiple criteria in one column.

    Quote Originally Posted by daddylonglegs View Post
    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!!?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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