Results 1 to 12 of 12
Thread: SUM() excluding errors

20140406, 05:04 #1
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140406, 05:10 #2
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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.

20140406, 12:12 #3
 Join Date
 Oct 2012
 Posts
 51
 Thanks
 1
 Thanked 7 Times in 7 Posts
Or =SUMIF([range],">0")
I see you have moved from Fukuoka!
Kevin

20140406, 13:45 #4
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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"}))Last edited by MartinM; 20140406 at 13:53.

20140407, 09:38 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,766
 Thanks
 5
 Thanked 0 Times in 0 Posts
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

20140407, 14:43 #6
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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.

20140408, 21:22 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,145
 Thanks
 40
 Thanked 212 Times in 198 Posts
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

20140409, 02:36 #8
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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.

20140409, 05:38 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,145
 Thanks
 40
 Thanked 212 Times in 198 Posts
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 functionLast edited by Maudibe; 20140409 at 06:51.

20140409, 09:24 #10
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,766
 Thanks
 5
 Thanked 0 Times in 0 Posts
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

20140409, 12:26 #11
 Join Date
 Oct 2012
 Posts
 51
 Thanks
 1
 Thanked 7 Times in 7 Posts
Martin
If you have Excel 2010>
=AGGREGATE(9,6,[range])
Ignores errors and text!
Elegant.
Kevin

The Following User Says Thank You to Kevin@Radstock For This Useful Post:
RetiredGeek (20140409)

20140409, 15:13 #12
 Join Date
 Jan 2004
 Location
 Ulaan Baatar
 Posts
 520
 Thanks
 1
 Thanked 43 Times in 41 Posts
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]))}Last edited by MartinM; 20140409 at 15:59.