Using a Blank cell as a Criteria in Excel Sumifs

This article will use blank cell as a criteria with SUMIFS functions to calculate the Sales.

 

Question: I have employees’ data that contains Employee, Region & Sales in separate column. Now, I want to derive the Sales figure on the basis of the blank cells in the corresponding columns.

 

Following is the snapshot of data we have:

 

img1

 

The scenario is if both the columns A & B are empty then the formula should add the numbers from column C (the corresponding cells) otherwise show zero.

  • In cell F3, the blank cell formula is
  • =SUMIFS(C2:C10,A2:A10,"=",B2:B10,"=")

 

img2

 

The above formula is showing 0 since there is no blank cells in column A & B.

  • If we delete cells A5:B6 then the result would be 900

 

img3

 

In this way, we can use Excel SUMIFS function to consider the blank cells as a condition.

Users are saying about us...

  1. Thanks for the “=” tip! But now I have fa follow up question. Horn my sum is fordmulas, I typically have the conditions in othafer cells which I then refer to in my formula. I tried putting “=” but that didn’t work..please forgive the terrible typing but the “enjoy this big” floater insists on covering up my typing so I can’t see the letters……st

    • Hi Cassie,

      We’ve modified the tip as the inverted commas font style were not allowing you to return the expected output.

      Do let us know if you still get error.

      Happy Learning,
      Team Excel Forum

  2. I’ve tried using “=” but #value message pop out.
    I want to total up in a row and if any of the cell are blank the answer shall remain Blank instead of 0 (Zero).
    I.e. :
    Person Round 1 Round 2 Total
    Andy 70 80 150
    Jessica — — — (BLANK)
    Ranhill — 90 90
    Ginny 90 70 160

    I also tried SUM, SUMIF, SUMIFS still error.
    Please assist.

    • HI Perrier,

      To solve this problem, you can simply use the sum formula along with custom formatting to display a blank space instead of zero. After applying the sum formula, select all the cells which contain the sum formula and press ctrl+ 1. Format Cells dialog box will appear, go to custom tab. You will see a label called “Type”. In the space below the TYPE label paste the following, “General;;(blank)” and then press ok. This formatting will replace all the zeros with a blank.

      Happy Learning,
      Site Admin

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube