+ Reply to Thread
Results 1 to 6 of 6

How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

  1. #1
    Registered User
    Join Date
    08-28-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    4

    How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    Hello excel experts,

    I am creating a model where I need to add values from a database where multiple criteria are present for each row. I am using a sumifs function but the problem occurs when I leave a criteria cell blank than sumifs function is looking in the database for only blank cells in criteria range. What can I do in sumifs function to ignore criteria requirement if criteria is blank?

    Thanks for all your help,

    Sam
    Last edited by Sam - excel rookie; 08-28-2011 at 04:29 PM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    Hello,

    instead of leaving the criteria cell blank, enter a * sign. This will include all rows for that criterion.

    cheers,

  3. #3
    Registered User
    Join Date
    08-28-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    thank you sir your solution works.

  4. #4
    Registered User
    Join Date
    08-28-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    Well I just tested the * symbal and the problem is that it does not include cells with numeric input. Any suggestions are welcome.

    Thanks

  5. #5
    Registered User
    Join Date
    08-28-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    also include blanks as well

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

    That means you want to do a sum of the whole range, without any condition, right?
    So, do a simple sum if the condition cell is blank.

    =if(A1="",Sum(B1:B100),Sumifs(B1:B100,C1:C100,A1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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