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
Get FREE Advanced Excel Exercises with Solutions!
