# SUM() excluding errors

• 2014-04-06, 05:04
MartinM
SUM() excluding errors
Excel 2003

I have a column of numbers, some of which display #N/A - something which would be awkward to workaround.

I want to sum the numerical values with a formula . . . SUM() simply comes up with the expected error so I tried using SUMIF() but couldn't get the expression for the Criterion to work.

Is this possible, or is there a better function I could use in a formula ?

Thanks

Martin
• 2014-04-06, 05:10
MartinM
OK - answered my own question :rolleyes:

In case anyone else needs to know . . .

=SUMIF([range],"<>#N/A")

I was complicating things by looking for ISERROR or ISNUMBER solutions.
• 2014-04-06, 12:12
Or =SUMIF([range],">0")

I see you have moved from Fukuoka! :)

Kevin
• 2014-04-06, 13:45
MartinM
Neat idea, but unfortunately there are some valid negative numbers in the range :-(

You are observant ! I came back to Wiltshire from Japan a week ago, back in August via Outer Mongolia (yes, really).

I'm still interested in some sort of NOT(ISERROR) or ISNUMBER solution but cannot get either to work . . .

PS this works, though, but is hardly elegant: =SUM(SUMIF([range],{"<0",">0"}))
• 2014-04-07, 09:38
fburg
Not sure how elegant this is but how about

{ =SUM(IF(ISNUMBER(range),range,0)) }

where the braces are from entering the formula as an array formula with CTRL+SHIFT+enter

I tried using SUMIF but had trouble entering the criteria.

Fred
• 2014-04-07, 14:43
MartinM
Thanks Fred, that works and is nicely general.

And we seem to be proving that there isn't what I'd call an elegant solution.
• 2014-04-08, 21:22
Maudibe
Martin,
Would a UDF be elegant for you?

Enter the formula in cell A9 =sumNum(A1:A8) to sum all the values in that are only numbers

Attachment 38309

Code:

```Public Function SumNum(rng As Range) Application.Volatile For Each cell In rng     If WorksheetFunction.IsNumber(cell) Then num = num + cell Next cell SumNum = num End Function```
• 2014-04-09, 02:36
MartinM
Elegant indeed :-)

The code looks exactly what SUMIF() does, except I can't get the syntax for using ISNUMBER() as a SUMIF() citerion.

I preferred a formula in this, as in other cases, because being able to understand where the cell's display is coming from is important to me in using data properly and avoiding mistakes. That approach is invalidated, of course, if the formula is too arcane to interpret easily - my definition of inelegant. Using named ranges helps - I've some long SUMPRODUCT() formulas which almost read like an English (or American) sentence.

I do use code extensively, for procedures, and UDFs where their function is as obvious as, say, SUM() when seen in a formula - eg a UDF for SUBTOTALIF() or my favourite: CELLABOVE which avoids endless issues with totals when ranges are extended.

Maybe we should make a collection of "elegant" UDFs here.
• 2014-04-09, 05:38
Maudibe
Martin,

Here is a breakdown of how the UDF compares with the cell formula =Sumif(Range, criteria, sum range)

Public Function SumNum(rng As Range)
: receives to function call and sets the range variable, rng, to the value of the range in the cell formula

Application.Volatile
: Any change to work sheet re-runs the macro to update the cell

For Each cell In rng
: Looks at each cell in the range passed to the function from the cell formula. The For Each cell in rng takes the place of the range

If WorksheetFunction.IsNumber(cell) Then num = num + cell
: If the value in the cell is a number then add it to the running total.
:The IF WorksheetFunction.IsNumber(cell) statement takes the place of the
criteria
:the num=num + cell adds the
sum range

Next cell : Loops back toFor each statement until cycle through all cells in range

SumNum = num : Sets SumNum with the running total then outputs as the returned cell value.

End Function : designates the end of the function
• 2014-04-09, 09:24
fburg
Hi Martin,

That was what I tried to do with SUMIF when I posted my alternative "elegant" solution. Couldn't get SUMIF to work with a criterion using ISNUMBER(). Something like
=SUMIF(range,ISNUMBER(range),range)
or
=SUMPRODUCT(range,ISNUMBER(range))
or
=SUM(range*ISNUMBER(range))

Fred
• 2014-04-09, 12:26
Martin

If you have Excel 2010>

=AGGREGATE(9,6,[range])

Ignores errors and text!

Elegant. :)

Kevin
• 2014-04-09, 15:13
MartinM
Elegant indeed.

I'm still using Excel 2003 - happy in all other respects though.

This construct at least ignores ALL errors rather than specific ones, works with both positive and negative values and in all languages:

=SUMIF([range],"<1E100")

or this array formula:

{=SUM(IF(ISERROR([range]),0,[range]))}