If AND OR formula

L

Les

I have the following formula:

=IF(AND(M3<TODAY()-35,W3="",X3="",Y3="",Z3=""),1,2)

Which basically says if the date entered in M3 is older than 35 days and W3,
X3, Y3, and Z3 have been not been populated then return a 1, otherwise return
a 2.

What I want it to say is if the date entered in M3 is older than 35 days and
the data is missing from just one of the cells W3, X3, Y3 or Z3, whichever
one it is, then return the values.

Thanks for helping.
 
R

Roger Govier

Hi Les

Try
=IF(AND(M3<TODAY()-35,OR(W3="",X3="",Y3="",Z3="")),1,2)

or
=IF(AND(M3<TODAY()-35,COUNTA(W3:Z3)<4),1,2)
 
D

David Biddulph

If you mean missing from one or more of W3 to Z3,
=IF(AND(M3<TODAY()-35,OR(W3="",X3="",Y3="",Z3="")),1,2)

If by "just one" you mean missing from one and only one of W3 to Z3,
=IF(AND(M3<TODAY()-35,COUNTIF(W3:Z3,"")=1),1,2)
 
Top