Adding sumifs with an or?

[ScottyLawrenceCO]

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()?

 

[Norie]

Do you have the category listed for each item as well?

 

[ScottyLawrenceCO]

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.

 

[barry houdini]

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))

 

[Aladin Akyurek]

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))

 

[ScottyLawrenceCO]

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!

 

[tweedle]

An excellent explanation of SumProduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[ScottyLawrenceCO]

Awesome resource on that link. Thanks for that too!