Summing is one of the most performed operations in Excel. In Excel, there are multiple functions that you can use to sum values. Among these, the SUMIF function can be used to sum values based on some predefined criteria. If there are any blank cells in your dataset then you might want to get rid of the blank cells in order to not get unwanted results. In this article, we will show you how to use the SUMIF function to sum not blank cells in Excel.
The above screenshot is an overview of the article, that shows how we can sum not blank cells using the SUMIF function in Excel.
Introduction to SUMIF Function in Excel
The SUMIF function looks for a criterion and sums values based on that criterion. The syntax of the SUMIF function is as follows:
SUMIF (range, criteria, [sum_range])
| Arguments | Requirement | Explanation |
|---|---|---|
|
range |
Required | The range of cells that we want to sum based on a criterion. |
| criteria | Required | The condition based on which the formula will sum values. |
| sum_range | Optional | The cells to sum. If this condition is omitted, then the cells in the range argument are summed. |
How to Use SUMIF Function to Sum Not Blank Cells in Excel: 2 Ways
We can use the SUMIF function to sum not blank cells in multiple ways in Excel. For this, we use the following dataset.
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)To 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.
Read More: How to Use Excel SUMIF with Blank Cells
2. Using Helper Column with LEN and TRIM Functions
Suppose you have a dataset with some blank cells. But 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 in the form of a space character. Excel recognizes this and treats the cell as non-blank rather than truly blank.
In such cases 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.
We use the following formula:
=SUMIF(B5:B16,"<>",C5:C16)
To solve this, we have to treat the cells containing spaces as empty cells. This can be done 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.
To make this work, we insert a new column after the Product column that will work as the helper column. We use the following formula in the helper column. This formula returns the number of characters left in a cell after removing the space.
=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
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,"<>")
2. Applying SUMPRODUCT Function
Another way 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.
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.
Conclusion
In this article, we have shown you the process of calculating the sum of non-blank cells with the SUMIF function in Excel. We achieved this by using the Not Blank operator and using the LEN and TRIM functions to make a helper column.
We have also shown you the methods of calculating the sum using other functions rather than the SUMIF function.
To show you how to calculate the sum of blank cells, we used the SUMIF function.
We hope that you find this article useful for calculating the sum of not blank cells with the SUMIF function in Excel. Practice the methods shown here to get a better understanding of the topic.
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!
