Use AND and OR to test a combination of conditions

When you need to find data that meets more than one condition, such as units sold between April and January, or units sold by Nancy, you can use the AND and OR functions together.

This example nests the AND function inside the OR function to search for units sold between April 30, 2011 and January 1, 2012, or any units sold by Nancy. You can see it returns True for units sold by Nancy, and also for units sold by Tim and Ed during the dates specified in the formula.

Here's the formula in a form you can copy and paste into a worksheet.

=OR(AND(C2>DATE(2011,4,30),C2<DATE(2012,1,1)),B2="Nancy")

Let's go a bit deeper into the formula. The OR function requires a set of arguments it can test to see if they're true or false. In this formula, the first argument is the result of the AND function with the DATE function nested inside it, and the second is "Nancy." You can read the formula this way: Test to see if a sale was made after April 30, 2011 and before January 1, 2012, or was made by Nancy.

Like the OR function, the AND function also returns either True or False. Most of the time, you use AND to extend the capabilities of another function, such as OR and IF. In this example, the OR function wouldn't find the correct dates without the AND function.

Use AND and OR with IF

You can also use AND and OR with the IF function.

In this example, salespeople don't earn bonuses until they sell at least $125,000 worth of goods, unless they work in the southern region where the market is smaller. In that case, they qualify for a bonus after $100,000 in sales.

=IF(OR(C4>=125000,AND(B4="South",C4>=100000))=TRUE,C4*0.12,"No bonus")

Let's look a bit deeper. The IF function requires three arguments to work. The first is a logical test, the second is the value you want to see if the test returns True, and the third is the value you want to see if the test returns False. In this example, the OR function and everything nested in it provides the logical test. You can read it as: Look for values greater than or equal to 125,000, unless the value in column C is "South," then look for a value greater than 100,000, and every time both conditions are true, multiply the value by 0.12, the commission amount. Otherwise, display the words "No bonus."

Try working with the examples shown above