Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


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.

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


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

Rabeya

Rabeya

Hello! I am Rabeya Islam. I have completed my B.Sc. in Computer Science and Engineering from East West University. Currently, I am working as an Excel and VBA Content Developer, and I research MS Excel casually. I have an interest in Research and Development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo