How to use the SUM with IFERROR in Excel – 4 Methods

If there is a cell with #N/A, #VALUE, or #REF! value in the selected range in which you want to apply the SUM function, it will return #N/A.

Overview of IFERROR SUM

Method 1 – Generic IFERROR SUM in Excel

Steps:

  • Select a cell to display the summation (F5).
  • Enter the following formula and press ENTER.
=SUM(IFERROR(C5:E5,0))

Generic IFERROR SUM

  • Drag down the Fill Handle to see the result in the rest of the cells.

AutoFill to Generic IFERROR SUM

Formula Breakdown

  • IFERROR(C5:E5,0) —> checks whether the first argument (C5:E5) holds errors. If it does, the IFERROR function returns 0. Otherwise, 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


Method 2 – Using the SUM and the IFERROR in Array

Steps:

  • Select a cell where you want to display the result (Here, D11).
  • Enter the following formula to see 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 whether the first argument ($C$5:$C$9/$D$5:$D$9) holds any errors. If it does, the IFERROR function returns 0. Otherwise, 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 (the “Total Quantity”)

Notes
  • While working with arrays,  remember to use the dollar sign ($) in front of the cell reference number.
  • When working with array values, don’t forget to press  Ctrl + Shift + Enter  to extract results.
  • After pressing  Ctrl + Shift + Enter , you will notice that the formula is enclosed in curly braces {}, declaring it as an array formula. Don’t type the brackets {} , Excel does it automatically.

Read More: How to Use IF and IFERROR Combined in Excel


Method 4. Merging the ISERROR with the IF and the SUM Functions

Steps:

  • Choose a cell (F5) and enter the following formula to see the SUM.
=SUM(IF(ISERROR(C5:E5),0,C5:E5))

Merge ISERROR with IF and SUM Functions

  • Drag down the Fill Handle to see the result in the rest of the cells.

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

Read More: Excel ISERROR vs IFERROR Functions


Method 4 – Use the SUM and the IFERROR in Separate Sheets

Save the previous dataset in a new sheet (“Data Sheet”).

Dataset of Use SUM with IFERROR from Separate Sheet

In another worksheet (“Separate Dataset”), save space for the result value.

Steps:

  • Select C5 and enter the following formula to get the sum considering #N/A, #VALUE, or #REF! as 0.
=SUM(IFERROR('Data Sheet'!C5:E5,0))

Use SUM with IFERROR from Separate Sheet

  • Drag down the Fill Handle to see the result in the rest of the cells.

AutoFill to Use SUM with IFERROR from Separate Sheet


Download Practice Template

Download the free practice Excel template here and practice.


You May Also Like To Explore

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo