Sum If Not Blank – Excel & Google Sheets

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

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.

To sum rows with non-blank cells, we exclude Scores with missing Player names. We use the criteria “not equal to blank” (“<>”) inside the SUMIFS Function.

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, linebreaks, 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:

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 to identify Players with names.

The TRIM Function removes the extra spaces from the start and end of each cell’s value and the LEN Function then counts the number of remaining characters. If the result of the LEN Function is 0, then the Player name must have been blank or only made of spaces:

We apply the SUMIFS Function to the helper column (Summing if greater than 0), and it now calculates the sum accurately.

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

If it is required to treat any cells containing only spaces the same way as if they were blank, but using a helper column is not appropriate, then we can utilize the SUMPRODUCT Function in combination with the LEN and TRIM Functions to sum data relating to cells containing non-blank Player names:

In this example, we use the SUMPRODUCT Function to perform complicated “sum if” calculations. Let’s walk through the formula.

This is our final formula:

First, the SUMPRODUCT Function lists the array of values from the two cell ranges:

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.