### Sum If Not Blank – Excel & Google Sheets

In this Article

*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.

1 |
=SUMIFS(C3:C8,B3:B8,"<>") |

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:

1 |
=SUMIFS(D3:D9,B3:B9,"<>") |

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:

1 |
=LEN(TRIM(B3)) |

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

1 |
=SUMIFS(E3:E9,D3:D9,">0") |

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:

1 |
=SUMPRODUCT(--(LEN(TRIM(B3:B9))>0),D3:D9) |

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:

1 |
=SUMPRODUCT(--(LEN(TRIM(B3:B9))>0),D3:D9) |

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

1 |
=SUMPRODUCT(--(LEN(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))>0),{25; 10; 15; 5; 8; 17; 50) |

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

1 |
=SUMPRODUCT(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})>0),{25; 10; 15; 5; 8; 17; 50) |

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

1 |
=SUMPRODUCT(--({1; 1; 0; 1; 0; 2; 0}>0),{25; 10; 15; 5; 8; 17; 50) |

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

1 |
=SUMPRODUCT(--({TRUE; TRUE ; FALSE; TRUE; FALSE; TRUE; FALSE}),{25; 10; 15; 5; 8; 17; 50) |

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

1 |
=SUMPRODUCT({1; 1; 0; 1; 0; 1; 0},{25; 10; 15; 5; 8; 17; 50) |

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:

1 |
=SUMPRODUCT({25; 10; 0; 5; 0; 17; 0) |

Finally, the numbers in the array are summed together

1 |
=57 |

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.