1. ## 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

2. OK - answered my own question

In case anyone else needs to know . . .

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

I was complicating things by looking for ISERROR or ISNUMBER solutions.

3. Or =SUMIF([range],">0")

I see you have moved from Fukuoka!

Kevin

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

5. 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

6. 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.

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

SumNum2.png

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

8. 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.

9. 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

10. 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

11. Martin

If you have Excel 2010>

=AGGREGATE(9,6,[range])

Ignores errors and text!

Elegant.

Kevin

12. ## The Following User Says Thank You to Kevin@Radstock For This Useful Post:

RetiredGeek (2014-04-09)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•