SumNErr(TheRange)

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:

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.