Sum range of cells and ignore errors
This tutorial shows how to sum cells and ignore errors through the use of an Excel formula or VBA
Example: Sum range of cells and ignore errors
|
This formula uses the Excel SUM and IFERROR functions to sum the numbers from range (C5:C11), whilst ignoring all errors by treating those cells as zero values. This is an array formula, therefore, once you have entered the formula into a cell you will need to simultaneously press Ctrl + Shift + Enter to convert it into an array formula.
|
Sub Sum_range_ignore_errors()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim DataRng As Range
Dim ws As Worksheet
Dim Rng As Range
Dim DataRng As Range
Set ws = Worksheets("Analysis")
Set DataRng = ws.Range("C5:C11")
Set DataRng = ws.Range("C5:C11")
'sum cells whilst ignoring errors
For Each Rng In DataRng
For Each Rng In DataRng
If Not (IsError(Rng.Value)) Then
sumwitherror = sumwitherror + Rng.Value
End If
sumwitherror = sumwitherror + Rng.Value
End If
Next
ws.Range("E5") = sumwitherror
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to sum values, ignoring errors, by changing the range reference ("C5:C11") in the VBA code. In this example the range reference has been assigned to an object variable name 'DataRng', which you can change in the VBA code.
Each Cell in Range: In this example, the code will loop through each of the cells in the specified range. We have assigned an object variable name to each cell in range as 'Rng', which you can change in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to sum the numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to sum values, ignoring errors, by changing the range reference ("C5:C11") in the VBA code. In this example the range reference has been assigned to an object variable name 'DataRng', which you can change in the VBA code.
Each Cell in Range: In this example, the code will loop through each of the cells in the specified range. We have assigned an object variable name to each cell in range as 'Rng', which you can change in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to sum the numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial show how to sum cells from a specified range and ignore errors that exist in that range, through the use of an Excel formula or VBA
FORMULA
=SUM(IFERROR(range,0))
=SUM(IFERROR(range,0))
ARGUMENTS
range: A range of cells which you want to sum, ignoring errors.
range: A range of cells which you want to sum, ignoring errors.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Sum values if greater than | How to sum values that are greater than a specific value using Excel and VBA methods | |
| Sum values if less than | How to sum values that are less than a specific value using Excel and VBA methods | |
| Sum values if begins with | How to sum values if corresponding cells begin with a specific value using Excel and VBA methods | |
| Sum largest n numbers | How to sum the largest n numbers in a range using Excel and VBA methods | |
| Sum entire column | How to sum all values in a single column using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUM Function | The Excel SUM function returns the sum of all numbers in a specified range | |
| IFERROR Function | The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result |