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

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

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

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

wrote in message
oups.com...
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

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

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.

On Aug 3, 11:57 am, "Bob Phillips" wrote:
=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)

wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

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

=IF(Count(A1:E1)=5,SUM(A1:E1),"NA")


--
Regards,
Tom Ogilvy


" wrote:

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

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

That works.

thank.

yong

On Aug 3, 12:14 pm, Tom Ogilvy
wrote:
=IF(Count(A1:E1)=5,SUM(A1:E1),"NA")

--
Regards,
Tom Ogilvy