5 Ways To Excel Sum Ignore Errors

When working with Excel, ignoring errors can be a useful feature to have, especially when dealing with large datasets. There are several ways to excel sum ignore errors, and in this article, we will explore five of them.

Excel is a powerful spreadsheet software that allows users to perform various calculations, including summing up values in a range of cells. However, when there are errors in the cells, such as #N/A or #VALUE!, the SUM function may return an error instead of the desired result. This is where ignoring errors comes in handy.

Method 1: Using the SUMIF Function

The SUMIF function is a variation of the SUM function that allows you to sum up values based on a specific condition. You can use the SUMIF function to ignore errors by using the "ISNUMBER" function as the condition.

For example, suppose you have a range of cells A1:A10 that contains numbers and errors, and you want to sum up the numbers while ignoring the errors. You can use the following formula:

=SUMIF(A1:A10, ISNUMBER(A1:A10))

This formula will sum up the numbers in the range A1:A10 while ignoring any errors.

How it works

The ISNUMBER function checks if a cell contains a number, and returns TRUE if it does, and FALSE if it doesn't. The SUMIF function then sums up the values in the range A1:A10 only if the ISNUMBER function returns TRUE.

Method 2: Using the SUMIFS Function

The SUMIFS function is similar to the SUMIF function, but it allows you to specify multiple conditions. You can use the SUMIFS function to ignore errors by using the "ISNUMBER" function as one of the conditions.

For example, suppose you have a range of cells A1:A10 that contains numbers and errors, and you want to sum up the numbers while ignoring the errors and also ignoring any cells that contain the value "0". You can use the following formula:

=SUMIFS(A1:A10, A1:A10, ">0", ISNUMBER(A1:A10))

This formula will sum up the numbers in the range A1:A10 while ignoring any errors and also ignoring any cells that contain the value "0".

How it works

The first condition ">0" checks if a cell contains a value greater than 0, and the second condition ISNUMBER checks if a cell contains a number. The SUMIFS function then sums up the values in the range A1:A10 only if both conditions are met.

Method 3: Using the AGGREGATE Function

The AGGREGATE function is a powerful function that allows you to perform various calculations, including summing up values, while ignoring errors.

For example, suppose you have a range of cells A1:A10 that contains numbers and errors, and you want to sum up the numbers while ignoring the errors. You can use the following formula:

=AGGREGATE(9, 6, A1:A10)

This formula will sum up the numbers in the range A1:A10 while ignoring any errors.

How it works

The AGGREGATE function uses the following syntax: AGGREGATE(function_num, options, array). In this case, the function_num is 9, which corresponds to the SUM function, and the options is 6, which corresponds to ignoring errors. The array is the range A1:A10.

Method 4: Using the SUBTOTAL Function

The SUBTOTAL function is a function that allows you to perform various calculations, including summing up values, while ignoring errors.

For example, suppose you have a range of cells A1:A10 that contains numbers and errors, and you want to sum up the numbers while ignoring the errors. You can use the following formula:

=SUBTOTAL(109, A1:A10)

This formula will sum up the numbers in the range A1:A10 while ignoring any errors.

How it works

The SUBTOTAL function uses the following syntax: SUBTOTAL(function_num, ref1, [ref2],...). In this case, the function_num is 109, which corresponds to the SUM function, and the ref1 is the range A1:A10.

Method 5: Using VBA Macro

You can also use a VBA macro to sum up values while ignoring errors. Here is an example of a VBA macro that sums up the values in a range while ignoring errors:

Sub SumIgnoreErrors()
    Dim rng As Range
    Dim sum As Double
    sum = 0
    
    For Each rng In Selection
        If IsNumeric(rng.Value) Then
            sum = sum + rng.Value
        End If
    Next rng
    
    MsgBox "The sum is: " & sum
End Sub

This macro loops through each cell in the selected range, checks if the cell contains a number using the ISNUMERIC function, and if it does, adds the value to the sum.

How it works

The macro uses a For Each loop to iterate through each cell in the selected range. For each cell, it checks if the cell contains a number using the ISNUMERIC function, and if it does, adds the value to the sum.

Gallery of Excel Sum Ignore Errors:

Excel Sum Ignore Errors Image Gallery

We hope this article has helped you learn how to excel sum ignore errors using different methods. Whether you're using the SUMIF function, SUMIFS function, AGGREGATE function, SUBTOTAL function, or VBA macro, you can now easily sum up values while ignoring errors in Excel.

Don't forget to share this article with your friends and colleagues who may find it helpful. If you have any questions or need further assistance, feel free to ask in the comments section below.