How to Ignore Blank Cells in Excel Sum (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

This article will show you how you can ignore blank cells in Excel sum. In the spreadsheet, sometimes we have to sum a data range that has blank cells. So, let’s see how we can ignore these blank cells.

Showing How to Ignore Blank Cell in Excel Sum


How to Ignore Blank Cells in Excel Sum: 6 Ways

We will show 6 methods that will help you to ignore blank cells in an Excel sum. For that, we can use some single functions such as SUM, SUMIF and AGGREGATE functions. Also, we can combine two or three functions to ignore these blank cells in an Excel sum.

  • First, we have to take a dataset with blank cells.
  • This below dataset includes Sales information of an Apple outlet that has some blank cells.

Dataset to Ignore Blank Cells in Excel Sum


1. Use of SUM Function to Ignore Blank Cells

The SUM function is the easiest tool that ignores blank cells in an Excel sum.

  • We want to find out the Total Sales for each seller.
  • Now, Add a new column to the dataset where you can calculate the Total Sales like the below image.

Adding Total Sales Column

  • Then, Write the following formula in H5 and Press ENTER.

=SUM(D5:G5)

  • This function finds the summation of a given range.

Assigning SUM Formula to Ignore Blank cells in Excel Sum

  • Thus, we get the Total Sales for the first seller.
  • Now, hold and drag the H5 cell downwards to copy the formula for all the cells.

Copying Formula in All Cells

  • Therefore, we have got Total Sales for all other sellers.

Showing Result Ignoring Blank Cells in Excel Sum


2. Using SUMIF Function to Ignore Blank Cells in Excel Sum

Now, we are going to show you how you can ignore the blank cells in Excel sum with the SUMIF function. For that, we are using the same dataset.

  • Write the following formula in H5 and Press ENTER.

=SUMIF(C5:G5,"<>")

  • This function finds cells with numeric values and returns their summation.

Inserting SUMIF Formula to Ignore Blank Cells in Excel Sum

  • Thus, we have our first sellers Total Sales.
  • Now, hold and drag the H5 cell downwards to copy the formula for all cells.

Copying Formula in All Cells

  • Therefore, we have ignored all the blank cells and have got the Total Sales for all sellers.

Showing Result Ignoring Blank Cells in Excel Sum


3. Combination of SUM & IFERROR

We can combine the SUM and IFERROR functions to ignore the blank cells in an Excel sum.

  • For that, Write the following formula in H5 and Press ENTER.

=SUM(IFERROR(C5:G5,""))

  • This formula ignores any blank cell and returns summation of a given range.

Inserting Combined Formula to Ignore Blank Cells in Excel Sum

  • Thus, we have ignored the blank cell and got the Total Sales for the first seller.
  • Now, hold and drag the H5 cell downwards to copy the formula for all other cells.

Copying Formula in All Cells

  • Therefore we ignored all blank cells and got the Total Sales for all sellers.

Showing Result Ignoring Blank Cells in Excel Sum


4. Merging SUM, IF & ISERROR Function

  • Now, let’s Merge SUM, IF & ISERROR functions to ignore the blank cells in Excel sum.
  • Insert the following formula in H5 and Press ENTER.

=SUM(IF(ISERROR(C5:G5),"",C5:G5))

  • This formula ignores any blank cell and returns summation of a given range.

Inserting Merged Formula to Ignore Blank Cells in Excel Sum

  • Therefore, we get the Total Sales for the first seller and the blank cell is ignored.
  • Now, Hold and Drag the H5 cell downwards to Copy the formula for all other cells.

Copying the Merged Formula for All Cells

  • Thus, we have ignored all blank cells and calculated the Total Sales for all sellers.

Showing Result Ignoring Blank Cells in Excel Sum


5. Use of AGGREGATE Function

In this section, we will use the AGGREGATE function to ignore blank cells in Excel sum.

And, we are using the same dataset.

  • First, write the following formula in H5 and Press ENTER.

=AGGREGATE(9,6,C5:G5)

  • This formula ignores any blank cell and returns summation of a given range.

Inserting Aggregate Formula to Ignore Blank Cells in Excel Sum

  • Thus, we have ignored the blank cell and got the Total Sales for the first seller.
  • Now, Hold and Drag the H5 cell downwards to Copy the formula for all other cells.

Copying the Formula for All Cells

Therefore, we have ignored all blank cells and found the Total Sales for every seller.

Showing Result Ignoring Blank Cells in Excel Sum


6. Finding Profit with IF Function

We can use the IF function to ignore blank cells with criteria.

Suppose, we take a dataset of a store showing different fruits items. We have the Cost Price and the Selling Price. But all the fruit items are not sold. So, some cells of Selling Price are blank. And, we have to find the Profit of those fruits that have been sold.

Dataset for Using IF Function to Ignore Blank Cells in Excel Sum

  • We can not find the Profit of the unsold fruit.
  • So, write the following formula in H5 and Press ENTER.

=IF(D5="","Not Sold",(D5-C5))

  • This function returns Not Sold if D5 is blank. Otherwise it returns the difference between D5 and C5.

Inserting IF Formula to Ignore Blank Cells in Excel Sum

  • Therefore, we have got the Profit for Apple.
  • Now, Hold and Drag the H5 cell downwards to Copy the formula for all other cells.

Copying the Formula for All Cells

  • Doing so, we have got the Profit for all those items that have been sold. And, for Banana and Coconut, it gives the statement Not Sold.

Showing Result Ignoring Blank Cells in Excel Sum


Practice Section

There is a Practice Section in the workbook where you can practice by yourself.

Showing Practice Section


Download Practice Workbook

You can download this practice workbook.


Conclusion

Thank you for making it this far. We have shown you how to ignore the blank cells in Excel sum with 6 different methods. We hope you find the content of this article useful. If you have queries about any procedure illustrated in this article, feel free to leave them in the comment section.


Related Articles


<< Go Back to Sum in Excel | Calculate in Excel | 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.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo