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.

**Table of Contents**Expand

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

**IFERROR(C5:E5,0)****—>**checks the first argument, range**C5:E5**, whether it holds any errors or not. If it does, then the**IFERROR**function returns**0**. If not, it returns**the value of the cell**.

**Output:***{100,0,64}***SUM(IFERROR(C5:E5,0))****SUM({100,0,64})****—>**returns the summation of the values in those cells.

**Output:***164*

**Read More:** How to Use IFERROR with VLOOKUP in Excel

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

**IFERROR($C$5:$C$9/$D$5:$D$9,0)****—>**checks the first argument, range (**$C$5:$C$9/$D$5:$D$9**), whether it holds any errors or not. If it does, then the**IFERROR**function returns**0**. If not, it returns**the calculated value of the cells**.

**Output:***{5;20;20;80;0}***SUM(IFERROR($C$5:$C$9/$D$5:$D$9,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)

**Notes**

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

**Read More:** How to Use IF and IFERROR Combined 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**

**ISERROR(C5:E5)****—>****The ISERROR****function**evaluates the cell range (**C5:E5**) and returns**TRUE**if there is an error, and if not, it returns**FALSE**.

**Output:***{FALSE,TRUE,FALSE}***IF(ISERROR(C5:E5),0,C5:E5)**

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(IF(ISERROR(C5:E5),0,C5:E5))**returns the summation.

SUM({100,0,64}) —>

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

**You May Also Like To Explore**

- How to Use Conditional Formatting with IFERROR in Excel
- How to Use Multiple IFERROR Statements in Excel
- Excel IFERROR Function to Return Blank Instead of 0

**<< Go Back to Excel IFERROR Function | Excel Functions | Learn Excel**