Functions AND and OR are 2 logic functions that help you to create sophisticated logical tests.

Presentation of the functions

These functions are very useful with logical tests.

Basically, with a logical test, you compare two items only. But if you want to create a test with more than 1 condition, you must use this function. Examples are at the end of this article.

AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

=AND(test 1,test 2,test 3, ...)

For instance, you have a list of hotels with different criteria like the number of stars (classification), the price of the menu, ...

You want to select a hotel with

• At least 3 stars (B2>=3)
• The price of the menu is maximum 35 (C2<=35)
• And the hotel has a Fitness room (E2="Yes")

So the formula is

=AND(B2>=3,C2<=35,E2="Yes")

As you can see, the test returns TRUE only if all the function's arguments are TRUE.

OR function

A condition OR returns TRUE if one (or more) tests of the function is true.

= OR(test 1,test 2,test 3, ...)

XOR function

You can create an exclusive OR (only one test is true) with the XOR function. This function is generally useful for electronic projects.

Have a look at this article if you want to learn more about it.

Convert TRUE or FALSE to 0 or 1

Do you know that you can convert the result of a test (TRUE or FALSE) to a numeric value (1 or 0) 😉👍

Few situations solve with AND or OR functions

Let's take this customer file. You want to extract some customers according to some criteria.

Solve these questions

Let's consider these different cases.

1. How many clients are married with one child
2. How many clients are single or divorced
3. How many clients are married with an income above 75,000
4. How many clients are male, unmarried, and without children
5. How many clients are women with 1 or 2 children?
6. How many clients are married or in a relationship, with an income greater than or equal to 50,000, and have at least two children?

Answer

• The answer for the question 1 is

=AND(E2="Married",F2=1)*1

• The answer to question 2 is

=OR(E2="Single",E2="Divorced")*1

• The answer to question 3 is

=AND(E2="Married",D2>75000)*1

• The answer to question 4 is

=AND(C2="Man";E2="Single";F2=0)*1

• The answer to question 5 is

=AND(C2="Woman";OR(F2=1;F2=2))*1

• The answer to question 6 is

=AND(OR(E2="Married",E2="Relationship"),D2>=50000,F2>=2)

Include a test in an IF function

Once you have created your complex logical test with the functions AND or OR, you can include them in the function IF to customize the result returned.