Summing a range + other cell with N/A values
Summing a range + other cell with N/A values
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Summing a range + other cell with N/A values

  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! Following from previous problem that has been solved, I tried to apply the following solutions that work fine on the range M2:P2 but don't work when I include I2 in the range to be summed:

    {=SUM(IF(ISNUMBER(M2:P2,I2),M2:P2,I2),1)}

    =SUMIF(M2:P2,I2,"<>#N/A")

    What should I do to get around this problem?

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

    Default

    Don't worry, figured it out:

    =SUMIF(M2:P2,"<>#N/A")+SUMIF(I2:I2,"<>#N/A")

    I'm still curious as to why you need to split the ranges out though?

  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 09:04, Shane wrote:
    Don't worry, figured it out:

    =SUMIF(M2:P2,"<>#N/A")+SUMIF(I2:I2,"<>#N/A")

    I'm still curious as to why you need to split the ranges out though?
    You 2nd SUMIF is redundant. Use...

    =SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 09:00, Shane wrote:
    Hi! Following from previous problem that has been solved, I tried to apply the following solutions that work fine on the range M2:P2 but don't work when I include I2 in the range to be summed:

    {=SUM(IF(ISNUMBER(M2:P2,I2),M2:P2,I2),1)}

    =SUMIF(M2:P2,I2,"<>#N/A")

    What should I do to get around this problem?
    Hi Shane:
    I am not sure if I understand fully what you are trying to do -- but for summing just numbers for l2:p2,
    L2 ... 6
    M2 ... 2
    N2 ... 3
    O2 ... a
    P2 ... 5
    using the following formula

    =SUM(IF(ISNUMBER(L2:P2),L2:P2))

    results in 16 -- which is correct

    HTH

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    =SUM(IF(ISNUMBER(L2:P2),L2:P2))


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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

    Default

    Mark, I have tried your formula. It returns an incorrect answer. The formula I used with the 2 SUMIF functions returned the correct result so I can only assume that the second SUMIF statement is not redundant?

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Shane:
    In my first post I missed the fact that you did have some #N/A in your data. I still might be missing something else ... but
    How about the single sum formula:

    =sumif(L2:p2,"<>#N/A",L2:P2)

    for me this single formula gives the right result.

    HTH

    [ This Message was edited by: Yogi Anand on 2002-04-09 10:01 ]

  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 09:15, Shane wrote:
    Mark, I have tried your formula. It returns an incorrect answer. The formula I used with the 2 SUMIF functions returned the correct result so I can only assume that the second SUMIF statement is not redundant?
    Then your assumption would be incorrect. Using SUMIF with a single cell, and then adding (with + operator) the results is redundant. =1+SUM(1) is the same as =1+1; however, the former uses a superfluous function call.

    I can't diagnose your problem because I don't know what constitutes an "incorrect answer"?

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

  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

    Hi Mark. I have revisited and your solution does indeed work (I obviously mistyped something).

    Although the other solution does give the same result as your suggested formula, I agree with your assertion that my formula was overkill. I now understand the neatest solution - many thanks!

    Yogi, thanks for your input too.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Shane and Mark:
    Referring to Mark's compact solution in ...
    =SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)

    If we wanted to make it still more compact, use

    =sumif(M2:P2,"<>#N/A")+N(L2)



    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,823
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-09 21:05, Yogi Anand wrote:
    Hi Shane and Mark:
    Referring to Mark's compact solution in ...
    =SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)

    If we wanted to make it still more compact, use

    =sumif(M2:P2,"<>#N/A")+N(L2)
    Yogi,

    When L2=#N/A, N(L2) will return #N/A, so will the modified formula.The IF(ISNUMBER(I2),I2) is the only way to eliminate an error value L2 might come to have.

    Aladin

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
  •  

 

 
DMCA.com