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,

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,

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”, “Invalid” and so on. 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,

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”, “Invalid” and so on. 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.

---

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,

• 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,

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,

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

---

You May Also Like To Explore

• 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)
• COUNTIF Function in Excel (10 Suitable Applications)
• How to Use COUNTIFS Function in Excel (4 Examples)

The post How to SUM with IFERROR in Excel (4 Ways) appeared first on ExcelDemy.