How to Use Excel SUMIF with Blank Cells (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to use the SUMIF function to sum up values corresponding to blank or empty cells in Excel. Even though blank cells are usually considered insignificant, they can sometimes provide valuable information in a dataset. If this is the case, it may be required to sum the data for these cells. To learn how to perform this, read the article carefully.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


How to Use Excel SUMIF with Blank Cells (3 Easy Methods)

In this section, we will demonstrate 3 effective methods of using the SUMIF function to sum up values corresponding to blank cells or empty cells in Excel with appropriate illustrations. For example, let’s take a dataset where a charitable organization named Save the Poor has a list of donations.

Dataset of Excel SUMIF Blank Cells

Here, in the column of Donor Name, the blank cells represent those donors who don’t want to expose their names while donating. Now, we are interested in calculating the total anonymous donation, which means to sum up the Donation Amounts next to blank cells. To do that, we can use the following methods.


1. Summing Up Total Run of Unnamed Players

We can use the following formula, consisting of the SUMIF function, to sum up the donation amount corresponding to the blank cells.

=SUMIF(B5:B14,"",C5:C14)

Using SUMIF Function to Sum Up Blank Cells

After clicking Enter, you should see the following results.

Result After Applying the SUMIF Function

πŸ“Œ How Does the Formula Work?

SUMIF(B5:B14,””,C5:C14)

Here,

  • B5:B14- This is the range (Donor Name column) upon which the criteria will be checked.
  • β€œβ€β€“ As we need to look for blank cells in the B5:B14 range, we set the argument empty inside inverted commas.
  • C5:C14 is the sum range (Donation Amount). The SUMIF function only sums those cells in the range C5:C14 whose corresponding cells in B5:B14 are blank.

2. Summing Up Pseudo Blank Cells Using Trim Function and Helper Column

Sometimes we need to sum up values corresponding to cells that look blank or empty, but in reality, they contain white spaces. It can happen due to improper data extraction from other sources into Excel. For illustration, here we introduce some white spaces in the first two of three blank cells that we saw in the previous example. Hence, the SUMIF function will only take the last truly blank cell and display 100 as a result.

Result of SUMIF Function with Whitespace

Hence, to correct the result, we need to trim the whitespace using the TRIM function and store the result in a separate Helper Column.

=TRIM(B5)

Using TRIM Function in Helper Column to Remove White Space

In the above picture, we can see that we have used the TRIM function in cell C5 to remove the white spaces from both ends of the text (leading and trailing spaces).

Now, we use the Fill Handle feature to autofill the rest of the cells from C6 to C14.

Using Autofill Option to Autofill rest of the cells in Helper Column

Now, we apply the SUMIF function while using the Helper Column as the criteria range in cell F5.

=SUMIF(C5:C14,β€œβ€,D5:D14)

Using the SUMIF Function to Sum Pseudo Blank Cells

As a result, we will get our desired result, which is 550.

Result After Applying the SUMIF Function

Alternative: Summing Up Pseudo Blank Cells Without Helper Column

If you are not a fan of using the helper column, you can use the following formula consisting of the SUMPRODUCT, LEN, and TRIM functions to sum up the cells corresponding to all pseudo blank cells.

=SUMPRODUCT(--(LEN(TRIM(B5:B14))=0),C5:C14)

Use of SUMPRODUCT, TRIM & LEN Functions to Sum Pseudo Blank Cells

Consequently, you will get the same result.

Results of Using SUMPRODUCT, TRIM, & LEN Functions to Sum Pseudo Blank Cells

πŸ“Œ How Does the Formula Work?

  • TRIM(B5:B14)

This function will trim off all the leading and trailing spaces (white spaces) from the range of cells B5:B14.

  • LEN(TRIM(B5:B14))=0

This will logically test whether any cell from the range B5:B14 has a length of 0 (blank). It will return True for blank cells and False for non-blank cells.

  • (–(LEN(TRIM(B5:B14))=0)

The double dash (–) converts the Trues and Falses into 1s and 0s, respectively.

  • SUMPRODUCT(–(LEN(TRIM(B5:B14))=0),C5:C14)

The SUMPRODUCT function will multiply each element of the 1st array consisting of 1s and 0s with the corresponding elements of the 2nd array, which consists of Donation Amount and then sum up all the products together.


3. Use of VBA SUMIF to Sum Cells Corresponding to Blank Cells

Many users prefer to use VBA code to perform a task. In this example, we will apply VBA code to sum cells corresponding to blank cells in cell E5 in Excel that we have shown in the first example.

Dataset for Use of VBA Code to Sum Cells Corresponding to Blank Cells

Now, to use the VBA code, follow the steps below.

Steps:

  • Open the VBA Editor by clicking ALT+F11.

Opening VBA Editor by clicking Alt+F11

  • Now, click the left button on the mouse on the sheet name to open the sheet module.

Opening Sheet Module

  • Now, write the following code.

VBA Code for SUMIF Blank Cells in Excel

VBA Code Syntax:

Sub Sum_Blank()
Range("E5").Value = Application.WorksheetFunction.SumIf(Range("B5:B14"), "", Range("C5:C14"))
End Sub
  • Now, run the code by clicking F5. As a result, you will see the desired result on cell E5.

Results After Running VBA Code for SUMIF Blank Cells in Excel

πŸ“Œ How Does the Code Work?

The code utilizes the SumIf method of Application.WorksheetFunction. Of the arguments of the SumIf method, the 1st argument Range(β€œB5:B14”) is the range where criteria will be checked. The 2nd argument β€œβ€ implies that the method will look for blank/empty cells. Finally, the third argument,Β  Range(β€œC5:C14”) is the sum range.

The result is displayed on the E5 cell of the worksheet.


Sum Values Based on Non-Blank Cells

If you want to sum cells corresponding to non-blank cells from the dataset that we have used in the 1st example, you can do that only by slightly modifying the formula like this below.

=SUMIF(B5:B14,"<>",C5:C14)

SUMIF Non-Blank Cells

As a result, you will get the desired result.

Result After Summing Up Non-Blank Cells

πŸ“Œ How Does the Formula Work?

SUMIF(B5:B14,”<>”,C5:C14)

Here,

  • B5:B14 range is the criteria range upon which the criteria will be checked.
  • β€œ<>” is the criterion that checks for non-blank cells.
  • C5:C14 is the sum range.

Excel Sum Blank Cells As Zero

On occasion, instead of displaying the result of a sum as 0, it may be more practical to display the result as a blank cell in Excel. In this section, we will learn how to display zero sum results as blank cells in Excel. For illustration, suppose we have four columns, and in the bottom cells, their sums are calculated.

Data Set for Displaying Sum Zero as Blank Cells

Here, we can see that B14 and E14 have 0 sum values. Now, our target is to display them as blank cells. To do that, follow the steps below.

Steps:

  • Select the bottom row or the cells where you want to change the display format. Then right-click on the mouse. As a result, a context menu will appear. From the context menu, click on Format Cells.

Opening Format Cell Options

  • Now, a new window named Format Cells will appear. From the menu, click on Custom from the category tab in the left corner. Then on the Type bar, write General;General;;@ . Finally, click OK.

Writing Custom Format

  • As a result, you will see that the cells that contain 0 are being displayed as blank cells.

Results of Custom Formatting for Displaying Zero Sum as Blank Cells


Things to Remember

  • Be aware whether your dataset contains any white spaces or pseudo blank cells. If that is the case, then use the 2nd method.
  • For a clean dataset without pseudo blank cells, you can apply the 1st and 3rd methods to do your task.

Conclusion

This concludes the article on the use of the SUMIF function to sum data corresponding to blank cells in Excel. If you found this post useful, please share it with your friends. Please let us know if you have any other questions. Finally, please visit Exceldemy for more interesting Excel articles.

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo