Sum Values for Non-Blank Cells (SUMIF Not Blank) in Google Sheets

In Google Sheets, if you want to sum values for the cells which are not blank (where the corresponding cell is not empty). For this, you can use the SUMIF or SUMIFS functions.

This tutorial will teach us to write a formula using both functions.

In the below example, we have a list of names with the donation amount, and now you need to get the sum of the donation where you have a name.

Formula with SUMIF to SUM Values Cells which are Not Blank

You can use the below steps:

1. First, enter the SUMIF function in a cell.
2. After that, in the first argument, refer to the range with the names.
3. Next, in the “criteria” argument, enter “<>” to tell the formula to consider the non-blank cells only.
4. In the “[sum_range]” argument, refer to the range with the donation amount.
5. Ultimately, enter the closing parentheses and hit enter to get the result.

=SUMIF(A2:A13,"<>",B2:B13)

The moment you hit enter, it returns the sum for the cells which are not blank.

SUM for Actual Non-Empty Cells

The formula we have written above is perfect, but there could be a situation when this formula can give you the wrong information.

Let’s say you have a cell only with a space, and when you use the above formula, it will consider that cell as a non-blank cell. See the below example:

To get over this problem, we can use SUMIFS instead of SUMIF. In SUMIFS, you can specify the blank space in the criteria to exclude while calculating.

=SUMIFS(B2:B13,A2:A13,"<>",A2:A13,"<> ")

In this formula, we have specified two criteria, one is to exclude the blank cells, and the other is to exclude the cells with a space.

Yet There’s One More Problem

You know how to deal with the cells with a space, but if a cell has multiple blank spaces, then it’s still not possible to get the correct sum with the above formula.

But now, let’s fix all of this with a single formula. No matter how many spaces you have in a cell, you can still exclude them from the calculation.

=SUMPRODUCT(--(LEN(TRIM(A2:A13))>0),B2:B13)

In this formula, we have a combination of SUMPRODUCT, LEN, and TRIM. To understand this formula, you need to read the below points.

1. First, TRIM removes extra space from all the values.
2. After that, LEN returns the length of all the characters.
3. Next, the condition tests the value whether it’s greater than 0 or not.
4. Now, the double minus sign (–), converts TRUE and FALSE into 0 and 1.
5. In the end, SUMPRODUCT multiplies the arrays with each other and returns the sum for the cells that are not blank, excluding cells with spaces.

⮞ Get the Sample Sheet