Sum array with #N/A
 

Hi,

For some reason I can't get the following to work. I have got an array of
numbers in which some observations are #N/A (the equivalent of NA() ).

3
6
8
#N/A
4
#N/A
5

Normally I'd just sum this array by submitting
{=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A. What
do I need to do in order for the formula to return the value 26?

Thanks for your help.

=SUM(IF(NOT(ISERROR(a1:a7)),a1:a7))
array entered works for me if that helps

"Henrik" wrote in message
...
Hi,

For some reason I can't get the following to work. I have got an array of
numbers in which some observations are #N/A (the equivalent of NA() ).

3
6
8
#N/A
4
#N/A
5

Normally I'd just sum this array by submitting
{=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A.
What
do I need to do in order for the formula to return the value 26?

Thanks for your help.

Fair enough, but I guess I need to add another dimension to this (as to why I
didn't choose that approach in the first place). Let's say that sum the
numbers in column A when the value in columb B is greater than 9 and omit
observations with #N/A.

3 2
6 8
8 10
#N/A 12
4 14
#N/A 4
5 14

I propose the following approach (which doesn't work) even though, I thought
it did in the past:

{=SUM(A1:A7*ISNUMBER(A1:A7)*(B1:B79))},

As far as I know, there is no way to solve this using a sum(if()) combination.

"N Harkawat" wrote:

=SUM(IF(NOT(ISERROR(a1:a7)),a1:a7))
array entered works for me if that helps

"Henrik" wrote in message
...
Hi,

For some reason I can't get the following to work. I have got an array of
numbers in which some observations are #N/A (the equivalent of NA() ).

3
6
8
#N/A
4
#N/A
5

Normally I'd just sum this array by submitting
{=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A.
What
do I need to do in order for the formula to return the value 26?

Thanks for your help.

Hi
change the formula which create #NA. e.g.
=IF(ISNA(your_formula),"",your_formula)

--
Regards
Frank Kabel
Frankfurt, Germany

"Henrik" schrieb im Newsbeitrag
...
Hi,

For some reason I can't get the following to work. I have got an
array of
numbers in which some observations are #N/A (the equivalent of
NA() ).

3
6
8
#N/A
4
#N/A
5

Normally I'd just sum this array by submitting
{=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns
#N/A. What
do I need to do in order for the formula to return the value 26?

Thanks for your help.

Henrik wrote...
. . . Let's say that sum the numbers in column A when the value in
columb B is
greater than 9 and omit observations with #N/A.

3 2
6 8
8 10
#N/A 12
4 14
#N/A 4
5 14

I propose the following approach (which doesn't work) even though, I
thought it
did in the past:

{=SUM(A1:A7*ISNUMBER(A1:A7)*(B1:B79))}

As far as I know, there is no way to solve this using a sum(if())
combination.
...

Well, not if you confuse IF(x,y) with x*y. Try using a *REAL* IF().

=SUM(IF(ISNUMBER(A1:A7)*(B1:B79),A1:A7))


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=318871