SOLVED

IF, AND and OR

%3CLINGO-SUB%20id%3D%22lingo-sub-2376552%22%20slang%3D%22en-US%22%3EIF%2C%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376552%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(AND(OR(D21%3AG21%26gt%3B%3D6)%3BOR(D20%3AG20%26gt%3B%3D6)%3B%22OPEN%22%3B%22CLOSED%22)%2C%20the%20program%20claims%20there's%20an%20error%20in%20applying%20D21%3AG21%2C%20either%20to%20AND%20and%20OR.%20Am%20i%20able%20not%20to%20apply%20just%20%22D21%26gt%3B%3D6%3BE21%26gt%3B%3D6%3BF21%26gt%3B%3D6%3BG21%26gt%3B%3D6%22%20and%20make%20it%20like%20a%20D21%3AG21%20sequence%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2376552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376606%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060210%22%20target%3D%22_blank%22%3E%40Francisco_Morassutti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20missing%20the%20closing%20parenthesis%20of%20the%20AND%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(OR(D21%3AG21%26gt%3B%3D6)%3BOR(D20%3AG20%26gt%3B%3D6))%3B%22OPEN%22%3B%22CLOSED%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EP.S.%20I%20assume%20that%20you%20use%20comma%20as%20decimal%20separator%3B%20if%20not%2C%20you%20should%20probably%20replace%20the%20semicolons%20%3B%20with%20commas%20%2C%20in%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376628%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060210%22%20target%3D%22_blank%22%3E%40Francisco_Morassutti%3C%2FA%3E%26nbsp%3B%2C%20Hi%2C%20one%20way%20to%20use%20a%20range%20instead%20of%20individual%20cells%20in%20the%20formula%20that%20you%20are%20probably%20intending%20is%20to%20use%20COUNTIF%20function%3C%2FP%3E%3CP%3ESee%20attachment%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(COUNTIF(D20%3AG20%2C%22%26gt%3B%3D6%22)%26gt%3B0%2CCOUNTIF(D21%3AG21%2C%22%26gt%3B%3D6%22)%26gt%3B0)%2C%22OPEN%22%2C%22CLOSED%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

=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?

3 Replies

@Francisco_Morassutti 

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.

best response confirmed by allyreckerman (Microsoft)
Solution

@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.