Return to Excel Formulas List

Sum If Not Blank – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the SUMIFS Function to sum data related to non-blank or non-empty cells in Excel and Google Sheets.

sum if not blank

 

Sum if Not Blank

First, we will demonstrate how to sum data relating to non-blank cells.

We can use the SUMIFS Function to sum all Scores for Players with non-blank names using criteria (“<>”).

SUMIFS Not Blank

Treating Spaces as Blank Cells – With Helper Column

You need to be careful when interacting with blank cells in Excel. Cells can appear blank to you, but Excel won’t treat them as blank. This can occur if the cell contains spaces, line-breaks, or other invisible characters. This is a common problem when importing data into Excel from other sources.

If we need to treat any cells that only contain spaces the same way as if they were blank, then the formula in the previous example will not work. Notice how the SUMIFS Formula does not consider cell B9 below (”   “) to be blank:

SUMIFS NonBlank or Spaces Error

To treat a cell containing only spaces as if it were a blank cell, we can add a helper column using the LEN and TRIM Functions..

The TRIM Function removes the extra spaces from the cell.

The LEN Function counts the number of remaining characters. If the count is 0, then the “trimmed” cell is blank.

SUMIFS Not Blank Spaces Helper

Now use the SUMIFS Function to sum if count > 0.

SUMIFS Not Blank Spaces Helper Answer

The helper column is easy to create and easy to read, but you might wish to have a single formula to accomplish the task. This is covered in the next section.

Treating Spaces as Blank Cells – Without Helper Column

To accomplish all this with a single formula, we can use the SUMPRODUCT Function in combination with the LEN and TRIM Functions.

SUMPRODUCT Not Blank or Spaces

Let’s walk through the formula.

First, the SUMPRODUCT Function reads the cell values:

Then, the TRIM Function removes leading and trailing spaces from Player names:

The LEN Function calculates the lengths of the trimmed Player names:

With the logical test (>0), any trimmed Player names with more than 0 characters are changed to TRUE:

Next the double dashes (–) convert the TRUE and FALSE values into 1s and 0s:

The SUMPRODUCT Function then multiplies each pair of entries in the arrays to produce an array of Scores only for Player names that are not blank or not made only from spaces:

Finally, the numbers in the array are summed together

More details about using Boolean statements and the “–” command in a SUMPRODUCT Function can be found here

Sum if Not Blank in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

sum if not blank Google Function