Thanks:  0
Likes:  0

1. ## 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. ## Re: Adding sumifs with an or?

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

3. ## 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. ## 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. ## Re: Adding sumifs with an or?

Originally Posted by 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()?
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))

6. ## 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. ## Re: Adding sumifs with an or?

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

8. ## Re: Adding sumifs with an or?

Awesome resource on that link. Thanks for that too!

## User Tag List

#### Posting Permissions

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