How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Using SUMIF or ISBLANK function in Excel

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.


Using Excel SUMIF or ISBLANK Functions to Sum for Blank Cells: 2 Examples

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.

Dataset to Use SUMIF with ISBLANK

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))

SUMIF with ISBLANK

Formula Breakdown:

  • ISBLANK(E5:E14)The ISBLANK function will search for the empty cells in the Delivery Date column.
    • 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.
  • 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.

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)

SUMIF Blank Cells

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)

SUMIF Non-Blank Cells

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.
Note: If any cell has only a space, then the criteria will not be able to satisfy the condition of finding blank cells.

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.

Practice Sheet for SUMIF values with the ISBLANK function


Download Practice Workbook

You can download the practice workbook here.


Conclusion

This article provides a solution on how to SUMIF values with the ISBLANK function in Excel. You can suggest your thoughts about this article in the comment section below.


Related Articles


<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rabeya Islam
Rabeya Islam

Rabeya Islam, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the SOFTEKO for more than one and half years. She has written some articles for ExcelDemy. Currently, she is working as the team leader, oversees the day-to-day work, and leads the SQA team Excel Extensions project. She has built the workflow and the structure of the extension testing for the team. Her work and learning interests vary from Microsoft Office Suites, VBA, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo