How to get a conditional argument to check all cells in a array?

J

Just 4 Today

I am using Excel 2003.
I would like to check all cells in an array for a number. In a cell not in
the array I have the formula {=If(A1:A9=1," ",1)}. Currently if there is a 1
in A1 I get a blank. If there is a 1 in A2 to A9 I get a 1 not a blank. Why?
 
B

Bob Phillips

I don't think you need an array formula

=IF(COUNTIF(A1:A9,1)>0,"",1)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top