Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Summing lists that contain N/A without losing formulae

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! This is probably a common problem but I don't know the answer.

    I have a list of values, each based upon a lookup to another worksheet. Some of the values returned are good, others are N/A when no value is found. In order to sum this list, I typically past value these cells and replace "N/A" with "0", thus enabling the sum function.

    Is there a way to sum this list while keeping the lookup formulae, thereby avoiding the copy value & replace method I currently use?

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =if(isna(myvlookupformula),0,myvlookupformula)
    ~Anne Troy

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 07:29, Shane wrote:

    Is there a way to sum this list while keeping the lookup formulae, thereby avoiding the copy value & replace method I currently use?
    =SUMIF(range,"<>#N/A")

    where "range" is the cell range of your list

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

    Default

    =ISNA(A1) tests cell A1 for the NA error and will return a true or false answer.

    Substitute A1 with your current formula to test its result.

    =IF(ISNA(YOURFORMULA)=TRUE,0,YOURFORMULA)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Try =SUMIF(A2:A11,"<>#N/A")

    or


    Array enter with Ctrl-Shift-Enter (CSE)

    =SUM(IF(ISERROR(A1:A10),"",A1:A10))
    Array enter with Ctrl-Shift-Enter (CSE)

    Edit the references as necessary.
    With the array formula, you must Array enter after edit as well.
    or try

    =SUMIF(A2:A11,"<>#N/A")

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry guys, I misled you a little. I am actually using the formula "LARGE" rather than "SUM".

    Based on your advise, I tried using the formula =LARGEIF($C2:$I2,1,"<>N/A") but that yields the result #NAME?

    What would you recommend I try?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 07:53, Shane wrote:
    Sorry guys, I misled you a little. I am actually using the formula "LARGE" rather than "SUM".

    Based on your advise, I tried using the formula =LARGEIF($C2:$I2,1,"<>N/A") but that yields the result #NAME?

    What would you recommend I try?
    {=LARGE(IF(ISNUMBER(C2:I2),C2:I2),1)}

    or

    {=MAX(IF(ISNUMBER(C2:I2),C2:I2))}

    Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    [ This Message was edited by: Mark W. on 2002-04-09 08:03 ]

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also, I tried the formula

    =IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1))

    but it returns 0 which is not the correct result.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Shane, see my latest posting above!

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also, I tried the formula

    =IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1))

    but it returns 0 which is not the correct result.

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
  •