Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to SUM with IFERROR in Excel (4 Handy Ways)

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.

Overview of IFERROR SUM


Download Practice Template

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


4 Handy 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

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

Generic IFERROR SUM

AutoFill to Generic IFERROR SUM

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


2. Employ SUM with IFERROR in Array

If we apply division between two arrays having #N/A, #VALUE, or #REF! as values or 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))

Employ SUM with IFERROR in Array

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.

Similar Readings:


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

Merge ISERROR with IF and SUM Functions

  • Finally, AutoFill the rest cells in the Total column with Fill Handle.

AutoFill to Merge ISERROR with IF and SUM Functions

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))
    SUM({100,0,64}) —>
    returns the summation.
    Output: 164


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.

Dataset of Use SUM with IFERROR from Separate Sheet

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

Use SUM with IFERROR from Separate Sheet

  • Finally, use Fill Handle to AutoFill the remaining cells in the Total Marks column.

AutoFill to Use SUM with IFERROR from Separate Sheet


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo