sum only if all are numbers, return 'NA' if non-numbers exist. How?

[yuany77]

Table:
1 2 3 N 4
N N 2 4 5
1 2 3 4 5

I want to sum each row and expected results are NA, NA, 15 for 1st,
2nd and 3rd row, respectively.

I used IF(ISNUMBER(A1:E1),SUM(A1:E1),"NA") and then shift+ctrl+enter.
but seems the results are not correct. It always give me 6,11,15..

any mistake here?

thanks.

yy

 

[Bob Phillips]

=SUM(IF(ISNUMBER(A1:E1),A1:E1))

as an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[yuany77]

Hi Bob,
thanks but your fomula will have the same results as mine:
10
11
15.
I want to get "NA" for the first two rows.

 

[yuany77]

That works.

thank.

yong