Results 1 to 8 of 8

Adding sumifs with an or?

This is a discussion on Adding sumifs with an or? within the Excel Questions forums, part of the Question Forums category; I have a worksheet with two Sheets: 1 sheet for my report, and another with my Sales database. The database ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    7

    Default Adding sumifs with an or?

    I have a worksheet with two Sheets: 1 sheet for my report, and another with my Sales database. The database has brand names of products in column A and number of units sold in column B. I need to sum items by category by adding up all the sales of each brand name.

    My Units Sold Formula on my report is several SUMIF() functions strung together:

    Example: =SUMIF(Sales!A:A,"Huffy",Sales!B:B)+SUMIF(Sales!A:A,"Panasonic",Sales!B:B)+SUMIF(Sales!A:A,"RideKing",Sales!B:B)

    Since some of the products have 25 or 30 brand names, these formulas become a nightmare really fast.

    The SUMIFS function would work, but the extra criteria only seems to be connected with AND() and since a bike can't be a Huffy AND a Panasonic, it always brings back Zero. Is there a way to use a SUMIFS() that connects conditions with OR()?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,048

    Default Re: Adding sumifs with an or?

    Do you have the category listed for each item as well?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    7

    Default Re: Adding sumifs with an or?

    No, sorry, I didn't make that very clear. That is what I am trying to do. There aren't categories in the data, so I need to sum all brand names for each category. For example:

    Bikes: Huffy, Panasonic, MountainKing, etc.
    Candy: Reese's, Nestle, M&M

    So I am labeling the formula on my Summary sheet, "Bikes" then using a chain of SUMIF() to add each of the bike brands to get that summary. It works, but it's very complicated with this much data, so I was hoping there was a way to do

    =SUMIF(Brand,"Huffy" OR "Panasonic" or "Mountainking",Units Sold) or something that would at least function similarly.

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,946

    Default Re: Adding sumifs with an or?

    You can shorten your formula like this

    =SUM(SUMIF(Sales!A:A,{"Huffy","Panasonic","RideKing"},Sales!B:B))

    or if you have a list for each category, as Norie suggests, then you can use that, e.g. list all the possible brands for a specific product in Z1:Z10 and use

    =SUMPRODUCT(SUMIF(Sales!A:A,$Z$1:$Z$10,Sales!B:B))

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,115

    Default Re: Adding sumifs with an or?

    Quote Originally Posted by ScottyLawrenceCO View Post
    I have a worksheet with two Sheets: 1 sheet for my report, and another with my Sales database. The database has brand names of products in column A and number of units sold in column B. I need to sum items by category by adding up all the sales of each brand name.

    My Units Sold Formula on my report is several SUMIF() functions strung together:

    Example: =SUMIF(Sales!A:A,"Huffy",Sales!B:B)+SUMIF(Sales!A:A,"Panasonic",Sales!B:B)+SUMIF(Sales!A:A,"RideKing",Sales!B:B)

    Since some of the products have 25 or 30 brand names, these formulas become a nightmare really fast.

    The SUMIFS function would work, but the extra criteria only seems to be connected with AND() and since a bike can't be a Huffy AND a Panasonic, it always brings back Zero. Is there a way to use a SUMIFS() that connects conditions with OR()?
    Try...

    =SUM(SUMIF(Sales!A:A,{"Huffy","Panasonic","RideKing"},Sales!B:B))

    If you create a range housing the criteria set, name list, invoke:

    =SUMPRODUCT(SUMIF(Sales!A:A,List,Sales!B:B))
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    New Member
    Join Date
    Mar 2011
    Posts
    7

    Default Re: Adding sumifs with an or?

    I have no idea how the SUMPRODUCT function worked, but it does and that is fantastic. Thanks so much guys! You have literally just saved me dozens of hours!

  7. #7
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,555

    Default Re: Adding sumifs with an or?

    An excellent explanation of SumProduct:
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  8. #8
    New Member
    Join Date
    Mar 2011
    Posts
    7

    Default Re: Adding sumifs with an or?

    Awesome resource on that link. Thanks for that too!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com