Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing values without N/A (2000)

    I think I have had a formula regression or reversion back to a pre-brain functioning period and I cannot, for the life of me, figure out how to sum the cells in B4:B13 where the contents are not "#N/A"

    If have tried at least 15 different versions of =IF(NOT(ISNA(B4:B13)),SUM(B$:B13))) and other such. Any help?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summing values without N/A (2000)

    One possible solution
    =SUMIF(B4:B13,">0")+SUMIF(B4:B13,"<0")

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing values without N/A (2000)

    Mike

    Try this:

    =SUMIF(B4:B13,"<>#N/A")

    Brent

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing values without N/A (2000)

    Thanks, both of those look like they should work...guess it was too late last nite (or too early this morning I should say)!

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing values without N/A (2000)

    More general approach avoiding all errors is to use an array formula:

    =SUM(IF(ISERROR(Range),"",Range))

    Which of course can easily be adapted to:

    =AVERAGE(IF(ISERROR(Range),"",Range))
    =STDEV(IF(ISERROR(Range),"",Range))

    All Array entered with control-shift-enter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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