SumNErr is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This macro was originally created as I tend to use controlled errors in my formulas. As such, since I want to be able to ignore an error and still do some basic mathematical instructions I needed to write a whole set of “NErr” (No Error”) macros.
If you want to calculate the sum of 5 numbers, and one of the cells have an error in it, then the standard Excel functions will get seriously confused and just return the first error it encounters.
See the below example for how SumNErr works:
Scenario 1 | Scenario 2 | Scenario 3 | |
Value: A | 10 | 10 | 10 |
Value: B | 20 | 20 | # N/A |
Value: C | 30 | # N/A | 30 |
Value: D | 40 | 40 | # NAME? |
Value: E | 50 | 50 | 50 |
Sum(A:E) | 150 | # N/A | # N/A |
SumNErr(A:E) | 150 | 120 | 90 |
The formula for calculating the sum of the five rows is:
SumNErr(B2:B6)
Below is the source code for the macro
Function SumNErr(TheRange As Range) As Double 'The function sum's up a range excluding any errors it detects. 'Last updated by Denis Dell Dim i, j As Integer With TheRange For i = 1 To .Rows.Count For j = 1 To .Columns.Count If IsNumeric(.Cells(i, j).Value) Then SumNErr = SumNErr + .Cells(i, j).Value End If Next j Next i End With End Function
I can understand why Microsoft would not like to include the above kind of functionality in its standard functions. The amount of errors already found in spreadsheets, which is shocking, will just be exponentially higher should users be able to ignore “=+#REF!” errors without getting at least some warning or something.