
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

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.

Or =SUMIF([range],">0")
I see you have moved from Fukuoka! :)
Kevin

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"}))

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

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.

1 Attachment(s)
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

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.

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 reruns 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

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

Martin
If you have Excel 2010>
=AGGREGATE(9,6,[range])
Ignores errors and text!
Elegant. :)
Kevin

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]))}