logical test in if function

[Pierre]

How can i set the "logical test" for an if function to
refer to a range of cells. eg.

i want to say if(c1=01:021,0,1)but this formula does not
work (c1 must be equal to any one of the values in cells
o1 to o21)

 

[Bernie Deitrick]

Pierre,

=IF(COUNTIF(O1:O21,C1)>0,0,1)

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

How can i set the "logical test" for an if function to
refer to a range of cells. eg.

i want to say if(c1=01:021,0,1)but this formula does not
work (c1 must be equal to any one of the values in cells
o1 to o21)

There are several ways.

1. =IF(OR(C1=O1:O21),0,1) entered as an array-formula (hold down <ctrl><shift>
while hitting <enter>. XL will place braces {...} around the formula).

2. =IF(COUNTIF(O1:O21,C1),0,1) entered normally.


--ron