Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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

  2. #2
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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.

  3. #3
    Star Lounger
    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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; 2014-04-06 at 14:53.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,774
    Thanks
    5
    Thanked 1 Time in 1 Post
    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. #6
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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.

  7. #7
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,282
    Thanks
    46
    Thanked 255 Times in 235 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

  8. #8
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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.

  9. #9
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,282
    Thanks
    46
    Thanked 255 Times in 235 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 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
    Last edited by Maudibe; 2014-04-09 at 07:51.

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,774
    Thanks
    5
    Thanked 1 Time in 1 Post
    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. #11
    Star Lounger
    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

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

    RetiredGeek (2014-04-09)

  13. #12
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 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; 2014-04-09 at 16:59.

Posting Permissions

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