If Function

[Lizz45ie]

I am trying to add another criteria to a "IF function" that isn't working.
My current IF statement is:

IF(Or(AI3="A",And(AI3<>"A",AH3<>1,AL3="ZMO")),0,1)

this formula works fine but when I added AL3="USMC" to the end of the
formula it no longer returns the expected result. What do I need to add to
the formula so that it returns an answer if AL3="USMC" also. Any help is
appreciated.

 

[T. Valko]

It depends on where you want it. Is it part of the OR function? Part of the
AND function? Or, is it a separate condition?

 

[Luke M]

As AL3 can not equal both "ZMO" and "USMC" at the same time, you probably
need to insert another OR function. Also, it is redundant to check for
AI3="A", and AI3<>
"A".

=IF(OR(AI3="A",AND(AH3<>1,OR(AL3="USMC",AL3="ZMO"))),0,1)

 

[Gary Brown]

tRY...
IF(Or(AI3="A",And(AI3<>"A",AH3<>1,AL3="ZMO"),And(AI3<>"A",AH3<>1,AL3="USMC")),0,1)

By just adding the AL3="USMC", you are saying 'if AL3 = ZMO and USMC.
Obviously, it can't be both at the same time.

 

[Rick Rothstein]

Your question is not stated clearly enough. What exactly do you mean by
"returns an answer if AL3="USMC" also"... what does "also" apply to... the
OR or the AND part? Or is it an AND condition coupled with the OR? These are
the three possibilities as I see it. If you want it OR'ed with the other OR
conditions...

=IF(OR(AI3="A",AND(AI3<>"A",AH3<>1,AL3="ZMO"),AL3="USMC"),0,1)

or if you want it AND'ed with the other AND conditions...

=IF(OR(AI3="A",AND(AI3<>"A",AH3<>1,AL3="ZMO",AL3="USMC")),0,1)

or if you want it AND'ed with the OR condition...

=IF(AND(AL3="USMC",OR(AI3="A",AND(AI3<>"A",AH3<>1,AL3="ZMO"))),0,1)

One of these should be what you want (depending on how you meant "also" to
be interpreted).

 

[Lizz45ie]

Sorry for the confusion. AL3 can equal USMC or ZMO. I want to know how do I
include the AL3="USMC" in the formula if AL3="USMC"? I tried all three of
the suggested formulas, I got #Value! error.

 

[Rick Rothstein]

Maybe this is what you want then...

=IF(OR(AI3="A",AND(AI3<>"A",AH3<>1,OR(AL3="ZMO",AL3="USMC"))),0,1)

although most would probably prefer to write it like this...

=IF(OR(AI3="A",AND(AI3<>"A",AH3<>1,OR(AL3={"ZMO","USMC"}))),0,1)