Sourav Kundu
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.
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.
Read More: How to Find Average with Blank Cells in Excel (4 Easy Ways) 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. Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel We can combine the SUM and IFERROR functions to ignore the blank cells in an Excel sum. Read More: How to Make Empty Cells Blank in Excel (3 Methods) Similar Readings Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods) In this section, we will use the AGGREGATE function to ignore blank cells in Excel sum. And, we are using the same dataset. 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) 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. Read More: If a Cell Is Blank then Copy Another Cell in Excel (3 Methods) There is a Practice Section in the workbook where you can practice by yourself. 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.=SUM(D5:G5)
2. Using SUMIF Function to Ignore Blank Cells in Excel Sum
=SUMIF(C5:G5,"<>")
3. Combination of SUM & IFERROR
=SUM(IFERROR(C5:G5,""))
4. Merging SUM, IF & ISERROR Function
=SUM(IF(ISERROR(C5:G5),"",C5:G5))
5. Use of AGGREGATE Function
=AGGREGATE(9,6,C5:G5)
6. Finding Profit with IF Function
=IF(D5="","Not Sold",(D5-C5))
Practice Section
Conclusion
Related Articles
