SUMIF Non-Blank (Sum Values for Non-Empty Cells)

To sum the value for the non-blank cells, you need to specify a non-blank value (“<>” by using the greater than and lower than the operator) in the criteria argument of the function. With this, SUMIF will look only for the cells which are non-empty and only consider values from the [sum_range] according to that. Below, we have calculated the sum of donations with names.

Using SUMIF for Non-Blank Cells

1. First, in the cell, enter the =SUMIF(.
2. Now, in the range criteria, refer to the range A2:A13.
3. Next, enter criteria for non-blank cells by using “<>”.
4. After that, in the [sum_range] argument, refer to the range B2:B13.
5. In the end, enter the closing parentheses and hit enter to get the result.

When you enter the formula and hit enter, it returns the sum of the donation for only those cells where you have a name in the name column.

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

SUMPRODUCT to SUM Actual Non-Empty Cells

Let’s say you have a blank cell and in that cell, you have a space. Now, this cell looks like an empty cell, but not empty.

In this situation, SUMIF will calculate the sum for that cell as well.

To deal with this problem, you can use a combination of SUMPRODUCT, LEN, and TRIM. With this combination, you can create an array formula. See the example below.

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

When you enter this function, it removes those blank spaces from the cells using TRIM. But there’s one thing that you need to know about SUMPRODUCT. It can take an entire array in a single cell without using the CTRL + ALT + ENTER keys.

Now, let’s break this formula into five small parts to understand it and see how this amazing formula works.

In the first part, we used the TRIM and referred to the entire name column. TRIM takes values from the column and removes space from the cells where you only have space(s). These are those cells that have no value but seem like non-blank cells.

In the second part, we have LEN which counts the length of the characters from the name column. For all cells where you don’t have any value, the count of the characters would be zero.

As you can see, we have values above zero for all the non-empty cells.

In the third part, we used a greater than operator to compare the result return by the LEN and TRIM. And it converts the count into TRUE and FALSE. For count above zero, a TRUE and else FALSE.

In the fourth part, we have a double minus sign that converts these TRUE and FALSE values into 0 and 1. For TRUE, a 0 and FALSE a 1.

After that, in the fifth part, we have SUMPRODUCT with two arrays. And when these arrays get multiplied with each other, you get the sum only for the cells which are not blank and don’t have an invisible space.