Sanjida Ahmed
If there is a cell with #N/A, #VALUE, or #REF! value in the selected range where we want to apply the SUM function, it will return #N/A as output. But there is nothing to worry about. We can fix it quite easily. In this article, you will learn how to get the summation of the values with errors with the combination of the SUM function and the IFERROR function in Excel.
How to SUM with IFERROR in Excel: 4 Handy Ways
You can know about the SUM function and the IFERROR function of Excel from our other articles interlinked earlier. Here, you will learn how to implement those functions together in Excel to extract the sum value of data with errors, so that you can produce a dataset without errors.
1. Generic IFERROR SUM in Excel
Sometimes we need to know the sum of certain numeric data even though there may be errors in it. In that case, we will use IFERROR with SUM to have the result. Let’s go through the below section for more details.
Steps:
- Select a cell where you want to have the summation (i.e. F5).
- In that cell, write the following formula and press ENTER to have the total.
=SUM(IFERROR(C5:E5,0))
- Now, use Fill Handle to AutoFill the rest cells in the Total column.
Formula Breakdown
Output: {100,0,64}
Output: 164
2. Employ SUM with IFERROR in Array
If we apply division between two arrays having #N/A, #VALUE, or #REF! as values or 0 as a denominator and apply the SUM function afterward, it will return #DIV/0! as output. To know what to do in this case, just go through the following section.
Steps:
- First of all, select a cell where you want to have the result (we have picked the cell beside Total Quantity, Cell D11).
- In that cell, apply the following formula to have the total quantity.
=SUM(IFERROR($C$5:$C$9/$D$5:$D$9,0))
Formula Breakdown
Output: {5;20;20;80;0}
SUM({5;20;20;80;0}) —> returns the summation of those values.
Output: 125 (which is exactly the “Total Quantity” of the dataset of fruits)
- While working with arrays, always remember to put the dollar sign ($) in front of the cell reference number. As the range of the search value is fixed, you need to wrap it up with the dollar sign ($), so that every time, when you are calculating for the new cells, the search range won’t change.
- When working with array values, don’t forget to press Ctrl + Shift + Enter on your keyboard while extracting results. Pressing only Enter will work only when you are using Microsoft 365.
- After pressing Ctrl + Shift + Enter , you will notice that the formula bar enclosed the formula in curly braces {}, declaring it as an array formula. Don’t type those brackets {} yourself, Excel automatically does this for you.
Similar Readings:
- How to Use IFERROR with VLOOKUP in Excel
- How to Use IF and IFERROR Combined in Excel
- How to Use Conditional Formatting with IFERROR in Excel
3. Merge ISERROR with IF and SUM Functions
There is another traditional way to calculate SUM with errors, where the ISERROR function is utilized to extract the result. ISERROR also belongs to the same group of error-checking functions as IFERROR. Here, we will learn how to generate the SUM of values with errors combining the IF function and the ISERROR function.
Steps:
- Pick a cell (i.e. F5) and apply the following formula in that cell to have the SUM.
=SUM(IF(ISERROR(C5:E5),0,C5:E5))
- Finally, AutoFill the rest cells in the Total column with Fill Handle.
Formula Breakdown
Output: {FALSE,TRUE,FALSE}
IF({FALSE,TRUE,FALSE},0,C5:E5) —> returns 0 if the value is FALSE. Otherwise, it will return the value in the C5:E5 range.
Output: {100,0,64}
SUM({100,0,64}) —> returns the summation.
Output: 164
Read More: Excel ISERROR vs IFERROR Functions
4. Use SUM with IFERROR from Separate Sheet
Until now, we have been getting SUM values of error data using the IFERROR function in Excel from the same worksheet. But what if we have a large dataset where the errors are also plenty? Doing calculations in that same sheet is not going to be a wise thing to do as there are risks involved in data loss. We can have our dataset in one worksheet in Excel and calculate in another worksheet in Excel so easily.
Let’s consider the usual dataset that we have been using till now and store that dataset in a sheet called “Data Sheet” in Excel.
Now in another worksheet, suppose the name is “Separate Dataset”, save space only for the result value.
Steps:
- Pick a cell (i.e. C5) and apply the following formula in that cell to have the sum considering #N/A, #VALUE, or #REF! as 0.
=SUM(IFERROR('Data Sheet'!C5:E5,0))
- Finally, use Fill Handle to AutoFill the remaining cells in the Total Marks column.
Download Practice Template
You can download the free practice Excel template from here and practice on your own.
Conclusion
At the end of this article, I like to add that I have tried to explain 4 handy ways to calculate SUM with IFERROR. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
