This post will guide you how to sum range of cells ignoring Errors in Excel. How do I sum and ignore errors with a formula in Excel. How to sum range ignoring all error values with VBA macro in Excel.

1. Sum Range Ignoring Errors with Formula

Assuming that you have a list of data in range A1:C4, in which contain numeric values and the error values, such as: #NAME?, #DIV/0, etc. and you want to sum this range but ignoring all error values. How to do it. You can use an Excel Array formula based on the SUM function, the IF function and the ISERROR function to achieve the result. Like this:

=SUM(IF(ISERROR(A1:C4),"",A1:C4))

You need to type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard to change this formula as array formula.

2. Sum Range Ignoring Errors with VBA

You can also use an Excel VBA Macro to achieve the same result of summing a selected range of cells ignoring all error values. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

Function SumIgnoreErrors(rng As Range) As Double
    Dim cell As Range
    Dim total As Double
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            total = total + cell.Value
        End If
    Next cell
    
    SumIgnoreErrors = total
End Function

Go back to your Excel workbook and use the custom function:

=SumIgnoreErrors(A1:C4)

adjusting the range references as needed.

The VBA code will sum the range while intelligently ignoring cells containing errors.

3. Video: Sum Range Ignoring Errors

This Excel video tutorial where we’ll address the challenge of summing a range while ignoring errors. Join us as we explore two methods – one using a formula with the SUM, IF, and ISERROR functions, and the other employing VBA code

4. Related Functions

• Excel SUM function
  The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel ISERROR function
  The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….