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

Dataset Overview

To demonstrate these methods, we require a dataset containing blank cells. This below dataset includes Sales information of an Apple outlet that contains some blank cells.

Method 1 – Using SUM Function

Add a new column to your dataset where you want to calculate the Total Sales.

In cell H5, enter the formula:

=SUM(D5:G5)

This function finds the summation of the given range (D5:G5), ignoring blank cells.

Press Enter.
Drag the formula down to copy the formula for all the cells.

The Total Sales for all Sales Reporters are populated.

Method 2 – Using SUMIF Function

In cell H5, enter:

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

This function sums numeric values in the range (C5:G5), while ignoring blank cells.

Press Enter.
Drag the formula down to calculate Total Sales for all sellers.

We have ignored all the blank cells and calculated the Total Sales for all sellers.

Method 3 – Combination SUM & IFERROR

In cell H5, enter:

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

This formula ignores blank cells and returns the summation.

Press Enter.
Drag the formula down to get Total Sales for all sellers.

We ignored all blank cells and got the Total Sales for all sellers.

Method 4 – Merging SUM, IF & ISERROR

In cell H5, enter:

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

This formula ignores blank cells and calculates the Total Sales.

Press Enter.
Drag the formula down to cover all sellers.

We have ignored all blank cells and calculated the Total Sales for all sellers.

Method 5 – Using AGGREGATE Function

In cell H5, enter:

=AGGREGATE(9,6,C5:G5)

This function ignores blank cells and computes the Total Sales.

Press Enter.
Drag the formula down for all sellers.

We have ignored all blank cells and calculated the Total Sales for every seller.

Method 6 – Finding Profit with the IF Function

Suppose you have a dataset with Cost Price (C) and Selling Price (D).

In cell H5, enter:

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

This formula calculates profit for sold items, showing Not Sold for unsold items.

Press Enter.
Drag the formula down to find profit for all items.

We have calculated the Profit for all the sold items. For Banana and Coconut, it gives the statement Not Sold.

Practice Section

There is a Practice Section in the workbook where you can practice these methods.