If all cells in a range are N/A then ...
 

I have a formula that counts all the cells in a range that have "N" and
divides them by the number in cell N/10.

=COUNTIF(N7:N9,"N")/N10

However, sometimes all the cells in a range are N/A.

I want a formula that returns an "N/A" if all the cells in the range are
N/A. and only performs the above formula when all the cells are not N/A.

I hope that's clear.

Any help is greatly appreciated.

If all cells in a range are N/A then ...
 

=IF(COUNTIF(N7:N9,"#N/A")=COUNTA(N7:N9)+COUNTBLANK(N7:N9),NA(),COUNTIF(N 7:N9,"N")/N10)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Anastasio Jimenez" wrote in
message ...
I have a formula that counts all the cells in a range that have "N" and
divides them by the number in cell N/10.

=COUNTIF(N7:N9,"N")/N10

However, sometimes all the cells in a range are N/A.

I want a formula that returns an "N/A" if all the cells in the range are
N/A. and only performs the above formula when all the cells are not N/A.

I hope that's clear.

Any help is greatly appreciated.

If all cells in a range are N/A then ...
 

=IF(COUNTIF(N7:N9,"#N/A")=3,"#N/A",COUNTIF(N7:N9,"N")/N10)


"Anastasio Jimenez" wrote:

I have a formula that counts all the cells in a range that have "N" and
divides them by the number in cell N/10.

=COUNTIF(N7:N9,"N")/N10

However, sometimes all the cells in a range are N/A.

I want a formula that returns an "N/A" if all the cells in the range are
N/A. and only performs the above formula when all the cells are not N/A.

I hope that's clear.

Any help is greatly appreciated.

If all cells in a range are N/A then ...
 

Thanks guys, that did the trick!

"Teethless mama" wrote:

=IF(COUNTIF(N7:N9,"#N/A")=3,"#N/A",COUNTIF(N7:N9,"N")/N10)


"Anastasio Jimenez" wrote:

I have a formula that counts all the cells in a range that have "N" and
divides them by the number in cell N/10.

=COUNTIF(N7:N9,"N")/N10

However, sometimes all the cells in a range are N/A.

I want a formula that returns an "N/A" if all the cells in the range are
N/A. and only performs the above formula when all the cells are not N/A.

I hope that's clear.

Any help is greatly appreciated.