OR or AND with IF (sounds confusing!)

The OR function will return a result of TRUE if ANY of the criteria tested is true.  The syntax for =OR is =OR(logical1, [logical2],[logical3],……).  Note that there only needs to be one logical test, the rest are optional. 

Similarly, =AND will return a result of TRUE if ALL of the criteria tested are true.   The syntax for =AND is =AND(logical1, [logical2],[logical3],……).  Note that there only needs to be one logical test, the rest are optional. 

Here is an example of the OR function: 

The formula is testing whether L6 is greater than 25 OR L7 is “tom”.  If either case is true (L7 does = “tom”) then it will return a result of TRUE.  If we changed the OR to AND, here is what we would get: 

Since only one of the two criteria is true, the formula returned a result of FALSE.

Not too exciting, huh? 

OK, now let’s use these functions in conjunction with =IF: 

We have 5 employees and we are testing them against 3 criteria:
– have they been with us more than 14 years?
– is their salary greater than $50,000?
– did they sell more than $650,000? 

Using the OR function in the top example with an IF function, all met at least one criteria.  I’ve highlighted those that were true. 

Using the AND function in the bottom example with an IF function, only 2 met ALL the criteria.  I’ve highlighted those that fell short. 

The OR and AND functions can be very useful when combined with other Excel functions to validate or examine a variety or criteria. 

I’m sure you’ll find a myriad of uses for these two simple Excel tools. 

Happy Excelling! 

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply