# SUMIFS with "AND/OR" (Ignoring Blank Criteria)

1. ## SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Still doing my best to learn Excel.

I have a spreadsheet set up as follows:

Month Day Genre Sales

Jan Sat Concert 1,500
Feb Fri Play 700
Feb Fri Lecture 200
Feb Mon Musical 400
Mar Fri Other 300

In another sheet, I have a table with the following:

Month Day Genre TotalSales
(List) (List) (List)

In the TotalSales cell, I would like to sum the total amount of sales based on the criteria I choose from the lists. For instance, if I choose Jan for the month, I want TotalSales to report 1,500. If I choose Fri for Day, I want it to report 1,000.

But I also want to be able to choose Feb for Month, AND Fri for Day, leave the Genre list empty, and have TotalSales report 900. I can't seem to do this while leaving the Genre list cell empty. If I choose Play for Genre, it gives me 700. But leaving it blank gives me 0.

My current Function:

=SUMIFS('Events'!D:D,'Events'!A:A,A2,'Events'!B:B,B2,'Events'!C:C,C2)

Am I correct in using the SUMIFS function, and do I just need to add something to it? Or is there another way to do this?

2. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Use some Wild Cards!

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*",'Events'!C:C,"*"&C2&"*")

And you can do it!

* is a Wild Card value, meaning anything before what we're looking for, and anything after. When we're looking for nothing, this will allow it to match everything.

3. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Exactly what I'm looking for! Having an issue though.

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*")
>> Works great, does exactly what I need

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*",'Events'!C:C,"*"&C2&"*")
>> Always returns a value of 0

Syntax looks good and I've typed it and retyped it a dozen times -- also copy/pasted and changed the cell references. No matter what I choose in the drop down lists, if the function is longer than my first example above, it returns a value of 0. Any idea why this might be?

4. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Syntax looks good and I've typed it and retyped it a dozen times -- also copy/pasted and changed the cell references. No matter what I choose in the drop down lists, if the function is longer than my first example above, it returns a value of 0. Any idea why this might be?
Maybe there are no rows which meet all 3 criteria?

5. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

There are multiple rows that meet all three criteria for many different combinations. No matter what, I get a result of 0 if I have 3 or more criteria in the function.

Even leaving all the dropdowns blank (would should return ALL sales, correct?) shows a result of 0.

EDIT::

The third column (C) in my actual workbook is a number (in this case, the year, of which I have 5). Does this make a difference? If I skip that column and put the next column as the third criteria, it works fine.

6. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Originally Posted by SCLisi

EDIT::

The third column (C) in my actual workbook is a number (in this case, the year, of which I have 5). Does this make a difference? If I skip that column and put the next column as the third criteria, it works fine.
Wildcards don't work on numbers.

Try it without the wildcard on column C:

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*",'Events'!C:C,C2)

7. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

If you are able to provide a sample of your work or a reasonable recreation, we can assist you in identifying any discepancies in your data.

8. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

I took out the wildcards for column C and I still get a result of 0, even if I choose a year in the drop down list.

EDIT::

I'll throw together a sample.

9. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Here's a chopped up example of my workbook.

10. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Which formula do you need help with?

The one in H2 seems to work.

11. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

You're right, my mistake, if I select a year, it works. My issue is, without selecting a year, it returns a value of 0. Is there a way to return total sales for everything, like the rest of the criteria, without using wildcards?

12. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Try

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*",'Events'!C:C,IF(C2="","*",C2))

13. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Originally Posted by Ace_XL
Try

=SUMIFS('Events'!D:D,'Events'!A:A,"*"&A2&"*",'Events'!B:B,"*"&B2&"*",'Events'!C:C,IF(C2="","*",C2))
I tried this and it didn't give me the correct total (12,630), it gave me 11,152...?

14. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

Is the data in your posted sample file typical of your real data?

The reason I ask is that I don't see any need for the use of wildcards in the formulas.

You use wildcards when you want to evaluate if a substring is part of a larger string. None of your data seems to need that type of evaluation.

15. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

If I take the wild cards out, I can't leave the drop down lists blank or I get a 0 value, as explained above. Only with the wildcards can I leave a drop down blank and return all results for calculation.

EDIT::

Yes, the data is exactly like my real data, only fewer rows and some removed columns.

16. ## Re: SUMIFS with "AND/OR" (Ignoring Blank Criteria)

If there are no selections made then there are no criteria to evaluate and a result of 0 should be expected. What's wrong with a result of 0 when there are no criteria to evaluate?

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