+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question 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?
    Last edited by SCLisi; 09-25-2013 at 12:51 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #3
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question 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. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question 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.
    Last edited by SCLisi; 09-25-2013 at 02:37 PM.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Quote Originally Posted by SCLisi View Post

    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)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #8
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    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. #9
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

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

    Here's a chopped up example of my workbook.

    https://dl.dropboxusercontent.com/u/...readsheet.xlsx

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Which formula do you need help with?

    The one in H2 seems to work.

  11. #11
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    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. #12
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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. #13
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

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

    Quote Originally Posted by Ace_XL View Post
    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. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #15
    Registered User
    Join Date
    01-09-2012
    Location
    Elkhart, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    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.
    Last edited by SCLisi; 09-26-2013 at 09:56 AM.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. can a "MINIF" formula be built, equivalent to "SUMIFS"?
    By jrtaylor in forum Excel General
    Replies: 4
    Last Post: 10-22-2011, 12:44 PM
  2. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  3. SUMIFS function ignoring ""
    By dtrudo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2010, 05:05 PM
  4. Replies: 0
    Last Post: 07-09-2009, 04:07 PM
  5. Replies: 3
    Last Post: 12-14-2006, 01:36 PM

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