Rabeya Islam
In this article, you will learn how to SUMIF values with the ISBLANK function in Excel. Sometimes, in the dataset, we have empty cells to check and add values satisfying any condition. Related to this, the main purpose of this article is to check empty cells and return the sum of the respective values from the dataset.
The above screenshot is an overview of the article, representing the application of SUMIF with the ISBLANK function in Excel. You’ll learn more about the methods in the following sections of this article.
Download Practice Workbook
You can download the practice workbook here.
2 Examples to Sum for Blank Cells Using SUMIF or ISBLANK Function in Excel
SUMIF and ISBLANK functions can be used to sum for blank cells in Excel. Here, I will write how to apply these functions in a dataset. I used the Microsoft Excel 365 version in this tutorial.
I have a dataset of a company regarding delivered products, and details information such as ODER ID, Selling Item, Sales, and Delivery Date.
From this dataset, I need to find out the sales amount of products that were not delivered. Thus, I will use SUMIF with ISBLANK in 2 methods. You can follow any of the methods of your choice. Download our Excel workbook, modify data & exercise!
1. Combine ISBLANK with SUM and IF Functions of Excel to Get Sum for Blank Cells
In this example, I will explain how SUM, IF, and ISBLANK functions can be used together to get the required solution from this dataset.
Steps:
- Select cell B17, write down the following formula, and press ENTER.
=SUM(IF(ISBLANK(E5:E14), D5:D14))
Formula Breakdown:
- ISBLANK(E5:E14) → The ISBLANK function will search for the empty cells in the Delivery Date
- E5:E14 → is the range of the cells where the ISBLANK function will search for empty cells.
- Output → Range of cells where TRUE and FALSE are stored.
- E5:E14 → is the range of the cells where the ISBLANK function will search for empty cells.
- IF(ISBLANK(E5:E14), D5:D14) → This will return FALSE if the cell contains FALSE and return the value of the respective Sales column if the cell contains TRUE.
- Output → Range of cells with Sales value and FALSE.
- SUM(IF(ISBLANK(E5:E14), D5:D14)) → becomes
- SUM(D6, D11, D14) → and adds the selected cells or cell range where the cell has values.
- Output → $2046.00.
- SUM(D6, D11, D14) → and adds the selected cells or cell range where the cell has values.
Read More: How to Use ISBLANK Function to Check If Cell Is Blank in Excel
2. Insert SUMIF Function Alone with Empty String Criteria to Add Values for Blank Cells in Excel
We can also use only the SUMIF function in blank cells to get the result from the dataset.
Steps:
- Select cell B17, write down the following formula, and press ENTER.
=SUMIF(E5:E14, "", D5:D14)
Formula Breakdown:
- SUMIF(E5:E14, “”, D5:D14) → The SUMIF function will search cells in the Delivery Date column and specify blank cells, to sum up, the corresponding Sales value.
- E5:E14 → is the range where the SUMIF function will search for blank cells.
- “” → is the criteria for finding blank cells in the range. It is used to search for finding zero-character-length cells.
- D5:D14 → is the range of cells to add up.
- Output → $2046.00.
The following formula also can be used to do the above task by substituting “” with =:
=SUMIF(E5:E14, "=", D5:D14)
Formula Breakdown
- SUMIF(E5:E14, “=”, D5:D14) → The SUMIF function will search cells in the Delivery Date column and specify non-blank cells, to sum up, the corresponding Sales value.
- E5:E14 → is the range where the SUMIF function will search for non-blank cells.
- “=” → is the criteria for finding non-blank cells in the range.
- D5:D14 → is the range of cells to add up.
- Output → $2046.00.
Read More: How to Use ISBLANK Function for Conditional Formatting in Excel
Things to Remember
- You can use the LEN and TRIM functions to treat cells with unseen characters as empty cells. But, LEN and TRIM are text functions, thus I am not directly applying these functions to my dataset.
- You can only use an ISBLANK function to check if there is an unseen character in my column. It will check whether the cell is empty or not, then return TRUE and FALSE based on the decision. In this way, I can find out if I have any spaces in my dataset column.
Practice Section
On the right side of each worksheet, you will find a practice section to practice on your own.
Conclusion
This article provides a solution on how to SUMIF values with the ISBLANK function in Excel. Follow our ExcelDemy page for regular tips and tricks regarding Excel. You can suggest your thoughts about this article in the comment section below.
Related Articles
- How to Use COUNTIF & ISNUMBER to Count Numbers in Excel
- Use ISNUMBER Function with IF and Then Statements in Excel
- How to Use ISLOGICAL Function in Excel (4 Examples)
- Use ISTEXT Function in Excel (8 Suitable Examples)
- How to Use ISEVEN Function in Excel (2 Suitable Examples)
- Use ISODD Function in Excel (4 Suitable Examples)
- How to Use ISNUMBER Function in Excel (7 Examples)
