Summing a range + other cell with N/A values

[Shane]

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?

[Shane]

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?

[Mark W.]

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)

[Yogi Anand]

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))

[Shane]

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?

[Yogi Anand]

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 ]

[Mark W.]

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 ]

[Shane]

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.

[Yogi Anand]

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)

[Aladin Akyurek]

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