Functions AND – OR

Functions AND and OR are 2 logic functions are used to create logical tests alone or with the IF function.

Presentation of the functions

These functions are use to enhance a logic test. Basically, with a logical test you compare 2 items only. But if you want to create a test with more items, you have to use one of these functions.

AND function

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

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

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

This is a function available only for Excel 2013 and up but is used is very limited (so don't really consider it)

With the OR function, one or more test have to be TRUE to return TRUE. But with the XOR function only one tests of the function must be true. If you have more than one test TRUE, the function will return FALSE

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

Convert TRUE/FALSE to 0/1

Whatever your test, single test or with the functions AND or OR, the test will return TRUE or FALSE

But, you can change that by multiply your test by 1 to return 1 if it's TRUE or 0 when it's FALSE

=8>1 => TRUE

=(8>1)*1 => 1

Example to use AND or OR functions

Let's take this customer file. You want to extract some customers in function of criteria for your business.

Let's consider these different cases.

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

  • The answer for the question 1 is


  • The answer for the question 2 is


  • The answer for the question 3 is


  • The answer for the question 4 is


  • The answer for the question 5 is


  • The answer for the question 6 is


Include a test in a IF function

These tests are very useful but return TRUE or FALSE it not the best way to present a result. The only way to customize a logic test is to insert it in a IF function

Related articles

Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/function-and-or/

Leave a Reply

Your email address will not be published. Required fields are marked *