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 where we want to apply the SUM function, it will return #N/A.

Steps:

=SUM(IFERROR(C5:E5,0))

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

Steps:

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

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.

To get the SUM of values with errors, you can combine the IF function and the ISERROR function.

Steps:

=SUM(IF(ISERROR(C5:E5),0,C5:E5))

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