Sometimes we need to know the sum of certain numeric data even though there may be errors in it. 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.

**Download Practice Template**

You can download the free practice Excel template from here and practice on your own.

**4 Ways of Getting SUM of Values with Errors Using IFERROR Function in Excel**

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

As you already know the generic syntax of the **IFERROR** function, which is,

`=IFERROR(value, value_if_error)`

Here,

**value** = First argument (required), cell reference which is checked for an error.

**value_if_error** = Second argument (required), customized value to return if the first argument evaluates an error.

Now, consider the following dataset, where we want to get the total “Marks” of the students of a classroom. But notice that, there are some errors (**#N/A**, **#VALUE!**, **#REF!**) in some cells in the “Marks” column beside the “Name” of certain students.

If we run the generic **SUM** formula here, then it will produce an output of **#N/A** error (see the picture below).

So, to catch the errors and suppress them, we need to run the **SUM** function along with an **IFERROR** function.

The steps of calculating **SUM** with** IFERROR** are shown below.

**Steps:**

- Select the cell where you want to have the result (we picked the cell beside “Total”,
**Cell C13**) - In that cell, write the following formula,

`=SUM(IFERROR(C5:C11,0))`

Where,

**C5:C11** = First argument of **IFERROR**, which is the cell range to check for errors.

**0** = Second argument of **IFERROR**, which will be returned if the **C5:C11** has errors.

You can set any customized value as a second argument, such as, ** “Not found”**,

**and so on.**

*“Invalid”***SUM**will only take the numeric return values to calculate generated from the first argument.

- Press
**Enter**.

It will produce the final sum values (e.g. **Total** 345) without any errors from the dataset with errors.

**Formula Breakdown:**

Let’s see the inside procedure of the formula,

**IFERROR(C5:C11,0)**-> checks the first argument, range**C5:C11**, 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**.

To understand the difference, see the picture attached in the output section below.

**Output: **

If you are not a fan of 1’s or 0’s, then you can set the second argument to any customized value that you want to return. To understand more, look at the picture below, where, instead of setting **0** as return type, we set **“Not Found”**. So the formula looks like the following,

**IFERROR(C5:C11,”Not Found”)**

and the **output** is,

**SUM(IFERROR(C5:C11,0))**-> becomes**SUM(100;0;80;75;0;90;0)**

**Output: 345** (which is exactly the total sum value of the dataset)

**2. SUM with IFERROR in Array Formula**

Consider the following dataset of arrays, where we need to find the “Total Quantity” of the fruits by dividing the total “Amount” of the fruit with the “Price” of each.

If we run the generic **SUM** formula here, it will throw an output of **#DIV/0!** error (see the picture below).

This is because the fruit “Kiwis” is holding **0** “Amount” of quantity with the “Price” of **0** for each.

So, to handle the error in this dataset of arrays, we need to run the **SUM** function along with an **IFERROR** function.

The steps of calculating **SUM** with** IFERROR** in the array are given below.

**Steps:**

- Select the cell where you want to have the result (we picked the cell below “Total Quantity”,
**Cell F5**) - In that cell, write the following formula,

`=SUM(IFERROR($C$5:$C$9/$D$5:$D$9,0))`

Where,

**($C$5:$C$9/$D$5:$D$9)** = First argument of **IFERROR**, which is the cell range to check for errors.

As, we need the “Total Quantity” of every type of fruit, so we need to divide the total “Amount” of the fruit with the “Price” of each to execute the result. Therefore, we put a **Division sign (/)** in between the range of the “Amount” of the fruits (**$C$5:$C$9**) and the “Price” of each fruit (**$D$5:$D$9**).

**0** = Second argument of **IFERROR**, which will be returned if the first argument, range (**$C$5:$C$9/$D$5:$D$9**), throws errors.

You can set any customized value as a second argument, such as, ** “Not found”**,

**and so on.**

*“Invalid”***SUM**will only take the numeric return values to calculate generated from the first argument.

- Press
**Enter**.

It will produce the final sum values (e.g. **Total Quantity** 125) without any errors.

**Formula Breakdown:**

Let’s see the inside procedure of the formula,

**IFERROR($C$5:$C$9/$D$5:$D$9)**-> 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**.

To understand more, see the picture attached in the output section below.

**Output: **

Again, if you are not a fan of 1’s or 0’s, then you can set the second argument to any customized value that you want to return (to understand more, see the above section-1).

**SUM(IFERROR($C$5:$C$9/$D$5:$D$9,0))**-> becomes**SUM(5;20;20;80;0)**

**Output: 125** (which is exactly the “Total Quantity” of the dataset of fruits)

**Must Keep in Mind While Working with Array**

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

**Combine SUMIF and VLOOKUP in Excel (3 Quick Approaches)****Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function****How to use COUNTBLANK function in Excel (3 Examples)**

**3. Generating the SUM of Values with Errors using ISERROR Function**

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.

Look at the same dataset below.

Previously, we solved it using the combination of the **SUM** and the **IFERROR** functions. But this time, we will try to solve it with the combination of the **SUM**, the **IF** and the **ISERROR** functions.

**Steps:**

- Same way, select the cell where you want to have the result (we picked the cell beside “Total”,
**Cell C13**) - In that cell, write the following formula,

`=SUM(IF(ISERROR(C5:C11),0,C5:C11))`

- Press
**Enter**.

It will produce the final sum values (e.g. **Total** 345) without any errors from the dataset with errors.

**Formula Breakdown:**

Let’s see the inside procedure of the formula,

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

**Output: **

As we have only **TRUE** or **FALSE** values to work with, this function must generally be paired with an **IF** function to execute the task properly.

**IF(ISERROR(C5:C11),0,C5:C11)**-> becomes**IF({FALSE:TRUE:FALSE:FALSE:TRUE:FALSE:TRUE},0,(C5:C11))**which means, if the first argument inside the**IF**function is**TRUE**, then return**0**; or if the first argument inside the**IF**function is**FALSE**, then return the**cell value**.

**Output:**

The** IF** function in Excel is structured in a way that it will always return a value whether it is **TRUE** or **FALSE**.

**SUM(IF(ISERROR(C5:C11),0,C5:C11))**-> becomes**SUM(100;0;80;75;0;90;0)**

**Output: 345** (which is exactly the total sum value of the dataset)

**4. Calculating SUM with IFERROR from Separate Sheet in Excel**

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 **“Result Sheet”**, save space only for the result value.

Now let’s bring the result from the **“Data Sheet”** to the **“Result Sheet”**.

**Steps:**

- Pick a cell (e.g.
**Cell E4**, beside the “Total Marks of Students”) from the sheet that you will be storing only the results (e.g. “Result Sheet”). - In that cell, just put a simple
**SUM-IFERROR**formula that you have already known from the previous discussions, formula such as,

`=SUM(IFERROR(C5:C11,0))`

But as this worksheet doesn’t have the written range of (**C5:C11**) to be considered, it will produce** 0** in the cell.

So now, all you have to do is, place the pointer of your mouse just before the array declaration in the formula (e.g. **C5:C11**), and select the other sheet (e.g. “**Data Sheet**”) that you want your values from. It will auto-generate that sheet (e.g. “**Data Sheet**”) into your working sheet (e.g. “**Result Sheet**”); hence all the data of that sheet (e.g. “**Data Sheet**”) will also be a property of the working sheet (e.g. “**Result Sheet**”).

Now the formula becomes,

`=SUM(IFERROR('Data Sheet'!C5:C11,0))`

- Press
**Enter**.

It will produce the final sum values (e.g. **Total 345**) of the data from the original worksheet (e.g. “**Data Sheet**”) to the working sheet (e.g. “**Result Sheet**”) without any errors.

**Conclusion**

This article explained in detail how to **SUM** values with errors combined with the **IFERROR** function in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.