Forum Discussion
IF, AND and OR
=IF(AND(OR(D21:G21>=6);OR(D20:G20>=6);"OPEN";"CLOSED"), the program claims there's an error in applying D21:G21, either to AND and OR. Am i able not to apply just "D21>=6;E21>=6;F21>=6;G21>=6" and make it like a D21:G21 sequence?
Francisco_Morassutti , Hi, one way to use a range instead of individual cells in the formula that you are probably intending is to use COUNTIF function
See attachment,
=IF(AND(COUNTIF(D20:G20,">=6")>0,COUNTIF(D21:G21,">=6")>0),"OPEN","CLOSED")
3 Replies
Francisco_Morassutti , Hi, one way to use a range instead of individual cells in the formula that you are probably intending is to use COUNTIF function
See attachment,
=IF(AND(COUNTIF(D20:G20,">=6")>0,COUNTIF(D21:G21,">=6")>0),"OPEN","CLOSED")amit_bhola, thank you so much, it was exactly what i was looking for.
You're missing the closing parenthesis of the AND function:
=IF(AND(OR(D21:G21>=6);OR(D20:G20>=6));"OPEN";"CLOSED")
P.S. I assume that you use comma as decimal separator; if not, you should probably replace the semicolons ; with commas , in the formula.
