Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Summing a range with errors in it

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I have a range that has for example #VALUE! and numbers in a range is there any way of summing the range and for it not to error, but give the sum of the numbers?

  2. #2
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Someone helped me with a similar problem I was having with #N/A.
    You might try this:
    =SUMIF(A1:A100,"<>#VALUE")

    If it worked for #N/A, I would think it would work for #VALUE

    Hope it helps

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Southampton UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that formula works, but remember its #value! not just #value

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, my bad

  5. #5
    Guest

    Default

    On 2002-03-02 09:29, Helen wrote:
    If I have a range that has for example #VALUE! and numbers in a range is there any way of summing the range and for it not to error, but give the sum of the numbers?
    If it's not beyond your control to have errors like #VALUE!, #DIV/0!, or #REF!, I'd suggest to hunt them down and eliminate their causes (excepting perhaps #N/A in the process, otherwise use for a range with different types of error the following array-formula:

    =SUM(IF(NOT(ISERROR(A1:A6)),A1:A6))

    To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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