Combining IF, OR and AND successfully

[Nanaia]

I'm trying to combine two IF formula's and seem to be on the struggle bus this morning because I can't get it to work right. Each formula works independently, but I can't figure out how to put them into one formula. Can anyone assist?

[=IF(OR(AND(CSV!AA3>0,CSV!AA3<71),CSV!AA3>121),"WJ"," ")]

[=IF(OR(AND(CSV!Z3>0,CSV!Z3<71),CSV!Z3>121),"WJ"," ")]

They are to give me the "WJ" as a result if any number in column Z or AA is [greater than zero and less than 71] or [greater than 121]. So basically give "WJ" as a result if it doesn't fall between 71 and 121. If you have an easier formula that works I'm all for using it.
Thank you in advance.

 

[hotabae]

Try:

=IF(OR(AND(CSV!AA3>0,CSV!AA3<71),CSV!AA3>121),"WJ",IF(OR(AND(CSV!Z3>0,CSV!Z3<71),CSV!Z3>121),"WJ",""))

The "fail" condition for =IF formulas can usually be used as an "ElseIf" condition.

=IF(logical_test,value_if_true,"ELSEIF")

So I literally just plopped the second if statement right into that 3rd argument slot.

 

[Nanaia]

When I tried that that it would only look at the second formula if the fist one is FALSE. I need both formulae to work simultaneously.

 

[hotabae]

Well as I see it there are 4 cases

First condition is true, Second is False -> "WJ"

First condition is false, Second is True -> "WJ"

First condition is true, Second is True -> "WJ"

First condition is false, second is False -> ""


Is this not the case?

This is how I wrote the formula.

 

[steve the fish]

You could just put the conditions of the first formulas OR within the OR of the 2nd formula. This

AND(CSV!AA3>0,CSV!AA3<71),CSV!AA3>121

inserted to become:

=IF(OR(AND(CSV!AA3>0,CSV!AA3<71),CSV!AA3>121,AND(CSV!Z3>0,CSV!Z3<71),CSV!Z3>121),"WJ","")

Or you could use a sumproduct like:

=IF(SUMPRODUCT(--((CSV!Z3:AA3>0)*(CSV!Z3:AA3<71))+(CSV!Z3:AA3>121)),"WJ","")

 

[Nanaia]

Steve, I finally came up with something similar. It is like yours but in a different order.

=IF(OR(AND(CSV!Z3>0,CSV!Z3<71),AND(CSV!AA3>0,CSV!AA3<71),CSV!Z3>121,CSV!AA3>121),"WJ","")

I've no experience using SUMPRODUCT so I will test that too. Thank you.