### Method 1 – Applying “Not Blank” Operator

The **Not Blank** operator can be used to find out whether a cell or a range of cells is blank or not. The **“<>”** symbol is used to specify the values that are not blank.

To sum values that are not blank in Excel using the **Not Blank** operator, we use the following **SUMIF** generic formula:

`=SUMIF(range,"<>",sum_range)`

Calculate the total quantity of available products or the cells that are not blank using the **SUMIF** function, we use the following formula:

`=SUMIF(B5:B16,"<>",D5:D16)`

__Formula Explanation:__

**SUMIF(B5:B16,”<>”,D5:D16)**

**B5:B16**is the range of cells that will be evaluated by the**SUMIF**function.**“<>”**is the criteria for the**SUMIF**function. It means “not equal to”, which means all non-blank cells in the range**B5:B16**.**D5:D16**is the range of cells from which the**SUMIF**function will sum values that meet the above criteria.

### Method 2 – Using Helper Column with LEN and TRIM Functions

A dataset with some blank cells. The blank cells are not truly blank, as these blank cells are represented by spaces. These cells appear empty visually making a cell not truly blank or empty because it still contains data as a space character. Excel recognizes this and treats the cell as non-blank rather than truly blank.

Using the **SUMIF** function to calculate the sum like the previous method will return an incorrect answer which is **327**. But the result should have been **247** this happens because there are spaces in the blank cells the formula counts them as non-blanks.

Use the following formula:

`=SUMIF(B5:B16,"<>",C5:C16)`

We must treat the cells containing spaces as empty cells by inserting a helper column.

**The TRIM function** removes spaces from the beginning and end of a cell. Meanwhile, **the LEN function** can be used to count the number of characters remaining in a cell. When the result of the **LEN** function is 0, this indicates that the cell is either truly blank or contains only spaces.

Insert a new column after the **Product** column that will work as the helper column. In the helper column, we use the following formula: After removing the space, this formula returns the number of characters left in a cell.

`=LEN(TRIM(B5))`

__Formula Explanation:__

**TRIM(B5)**

- Removes any leading or trailing spaces from cell B5

**LEN(TRIM(B5))**

- Counts the number of characters remaining in the text string.

We can now calculate the sum using the **SUMIF** function with the help of the helper column by using the following formula:

`=SUMIF(C5:C16,">0",D5:D16)`

## 3 Other Ways to Sum Not Blank Cells in Excel

### Method 1 – Using SUMIFS function

**The SUMIFS function** can be used as an alternative to **the SUMIF function** to calculate the sum of not blank cells in Excel. We use the same dataset to calculate the sum. We can use the following **SUMIFS** formula:

`=SUMIFS(C5:C16,B5:B16,"<>")`

### Method 2 – Applying SUMPRODUCT Function

To calculate the sum of not blank cells is by using **the SUMPRODUCT function**. We can use the following **SUMPRODUCT** formula:

`=SUMPRODUCT((B5:B16<>"")*C5:C16)`

__Formula Explanation:__

**SUMPRODUCT((B5:B16<>””)*C5:C16)**

**B5:B16<>”” **checks whether the cells in the range **B5:B16** is not blank. If a cell is not blank, the expression evaluates to TRUE and if a cell is blank, the expression evaluates to FALSE that results in:

**{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}**

**C5:C16** is the array of corresponding values for which we want to calculate the product.

The ***** operator is used to multiply each TRUE value in the logical array by the corresponding value in **C5:C16**. This results in a new array where each element is either zero or the corresponding value in **C5:C16**. The formula will become like the following and return a result of **247.**

`=SUMPRODUCT({28;30;0;27;30;0;26;0;27;26;28;25})`

The formula will calculate the sum by adding up these numbers.

### Method 3 – Combining SUM and FILTER Functions

We can use **the SUM function** with **the FILTER function** to calculate the sum of not blank cells. We use the following formula:

`=SUM(FILTER(C5:C16,B5:B16<>"",0))`

__Formula Explanation:__

**FILTER(C5:C16,B5:B16<>””,0)**

The **FILTER** function is used to filter the range **C5:C16** based on the condition that the corresponding cells in the range **B5:B16** are not empty.

**SUM(FILTER(C5:C16,B5:B16<>””,0))**

The **SUM **function then calculates the sum of all values in the resulting filtered array.

**Note:** The **FILTER** function is available for Office 365 and later versions.

## How to Sum Blank Cells with SUMIF Function in Excel

We have seen how to calculate the non-blank cells using the SUMIF function in Excel. We can use the SUMIF function to sum only the blank cells too. The following generic formula is used to calculate the sum of blank cells:

`=SUMIF(range,"",sum_range)`

We can utilize this formula in our previously used dataset to calculate blank cells. We use the following formula:

`=SUMIF(B5:B16,"",C5:C16)`

**Note:** If there are cells that appear visually blank but contain spaces, the formula may interpret them as non-blank cells and include them in the calculation. This could lead to unexpected errors in the result.

**Download Practice Workbook**

You can download the spreadsheet and practice the methods by working on it.

## Related Articles

- Excel SUMIF Function for Not Equal Criteria
- Excel SUMIFS with Not Equal to Text Criteria
- Excel SUMIF Not Working
- [Fixed!] Excel SUMIF with Wildcard Not Working

**<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel**