Use SUMIF to Sum Blank Values or Empty Cells

To use SUMIF to sum values for the blank criteria, you just need to specify a blank value in the criteria argument in the SUMIF function. With this, tell Excel to only consider cells where there’s no value. In the example, below we have three blank cells in the name column for which we need to sum the donation amount.

sumif-for-blank-values

In this tutorial, we will learn to use SUMIF or SUMIFS function to sum for blank values.

Write a formula to use SUMIF for Blank Values

  1. First, enter SUMIF in a cell where you want to calculate the sum.
  2. Now, refer to the Name column where you have blank cells.
  3. After that, enter double quotation marks (starting and closing).
  4. Next, refer to the Donation column from where you need to sum the values.
  5. In the end, hit enter to get the result.
sumif-formula-for-blank-values

=SUMIF(A2:A13,””,B2:B13)

A Problem You Might Face

There might be a situation where you have a range of cells where you have a cell or multiple cells with space as a value in the cell which looks like a blank cell, but not a blank cell.

In that case, SUMIF will ignore that cell and won’t include it while calculating the sum.

sumif-ignores-blank-cells-with-space

Here, you can create a formula with the help of SUMIFS and SUM to create a SUMIF OR.

sum-and-sumif

In this formula, you need to specify two criteria in the criteria1 argument using curly brackets.

  • Space
  • Blank
two-criterias-space-and-blank

When you do this, SUMIF considers both criteria simultaneously. And to get the result, you need to enter it as an array formula (use Ctrl + Alt + Enter).

=SUM(SUMIFS(B2:B13,A2:A13,{“”,” “}))

You can learn more about it here.

Get the File