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.

**Table of Contents**hide

## Download Practice Workbook

You can download this practice workbook.

## 6 Ways to Ignore Blank Cells in Excel Sum

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.

### 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.

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

`=SUM(D5:G5)`

- This function finds the summation of a given range.

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

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

**Read More: How to Find Average with Blank Cells in Excel (4 Easy Ways)**

### 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.

- Thus, we have our first sellers Total Sales.
- Now,
**Hold**and**Drag**the**H5**cell downwards to**Copy**the formula for all cells.

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

**Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel**

### 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.

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

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

**Read More: How to Make Empty Cells Blank in Excel (3 Methods)**

**Similar Readings**

**How to Remove Blanks from List Using Formula in Excel (4 Methods)****If Cell is Blank Then Show 0 in Excel (4 Ways)****How to Perform If Zero Leave Blank Formula in Excel (3 Methods)****Find If Cell is Blank in Excel (7 Methods)****How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas**

### 4. Merging SUM, IF & ISERROR Function

- Now, letâ€™s Merge
**SUM**,**IF**&**ISERROR function**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.

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

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

**Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)**

### 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.

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

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

**Read More: How to Set Cell to Blank in Formula in Excel (6 Ways)**

### 6. Finding Profit with IF Function

This **IF function** can be used 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.

- 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**.

- Therefore, we have got the
**Profit**for Apple. - Now,
**Hold**and**Drag**the**H5**cell downwards to**Copy**the formula for all other 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**.

**Read More: If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)**

## Practice Section

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

## 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. For more insight, you can visit our website ExcelDemy.com.

## Related Articles

**How to Find Sum If Cell Color Is Green in Excel (4 Easy Methods)****SUMIF vs SUMIFS in Excel (A Comparative Analysis)****How to Skip Blank Rows Using Formula in Excel (8 Methods)****Return Non Blank Cells from a Range in Excel (3 Suitable Ways)****How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)****Fill Blank Cells with Text in Excel (3 Effective Ways)****How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)**