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?

 

[Yogi Anand]

On 2002-04-09 23:49, Aladin Akyurek wrote:

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

Hi Aladin:
You are right! N(L2) will be useful for Numeric and No-numeric values only.
This is why I had earlier suggested the use of the formula:
=sumif(L2:P2,"<>#N/A",L2:P2) ... if we are summing L2:P2, naturally it can be shortened to:
=sumif(L2:P2,"<>#N/A")
This message was edited by Yogi Anand on 2002-04-10 12:18