There are two special functions, AND and OR, that make this easy to do. Let’s take a look. In this first worksheet, we have a list of employees. Let’s assume that you need to group these employees into two groups. Group A includes employees in Sales and Marketing and group B includes employees in Fulfillment, Support, or Engineering. While this could be done using nested IF statements, an easier way to calculate these groups is to use the IF function together with the OR function. Start off normally with IF and an open parentheses. For the logical test, we want to use the OR function which simply accepts a series of logical tests. If any test returns TRUE, the OR function will return TRUE. I can write the first logical test as “F5 = Sales” and then add a comma, and write the second test as “F5 = Marketing.” Then I close the parentheses for OR. For value if “true” I’ll use A, and for the value if “false” I’ll use B. When I copy the formula down, we’ll see the groups we need. The way this works is that the OR function behaves like a single logical test for the IF function: it returns TRUE if F5 is either Sales or Marketing. Now let’s look at another example which contains a list of houses for sale. Imagine that you have a big list and you want to quickly mark the properties you’re interested in. You’re looking for a house that has 3 or more bedrooms, is less than 3000 square feet, and was built after 1979. In this case, we can’t use IF with OR. We need to use IF with the AND function. The AND function works like the OR function; simply enter each logical test separated by commas. In this case, D5 needs to be greater than or equal to 3; F5 needs to be less than 3000; and G5 needs to be greater than 1979. For the value if true, I’ll simply use “Check”, and for the value if false, I’ll use empty double quotes which will appear as blank cells on the worksheet. When I copy the formula down, you can quickly see which houses meet the criteria. Like the OR function, the AND function evaluates to a single TRUE or FALSE value. However, unlike OR, the AND function requires that all tests return TRUE.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
