# AND – OR Functions

Functions AND and OR are 2 logic functions that are mostly used with the IF function.

## Presentation

These functions are often attached to the IF function but can also be used with tools of data validation or conditional formatting updates.

### 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, …)

Excel 2010 allows up to 255 arguments in functions AND & OR 😉

## Exercise

In the following file, you have various informations about your customers. You want to extract of this file a part of your customers on certain criteria.

If the criteria match your expectations, you will enter in column G 1 if you rest is True and 0 if test is not conclusive. Then, you make a SUM of the column G to see how many customers match your criteria.

Try to solve all these issues in the Excel workbook below.

• 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.

If your logical test is based on a string you must write your string between double-quotes, if it’s a numeric, leave it like it is

The answer for the question 1 is:

=IF(AND(E2=”Married”,F2=1),1,0)

The answer for the question 2 is:

=IF(OR(E2="Single",E2="Divorced"),1,0)

The answer for the question 3 is:

=IF(OR(E2="Married",D2>75000),1,0)

The answer for the question 4 is:

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

The answer for the question 5 is:

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

The answer for the question 6 is:

=IF(AND(D2>=50000;F2>=2;OR(E2="Married";E2="relationship"));1;0)

1. ##### Anonymous

where is the table i c’t find anyone

1. ##### Frédéric LE GUEN

What is your question?

2. ##### ChrisB

Hi. My students and I agree with both Cska and Inbal above.
Q3 is =IF(AND(E2=”Married”,D2>75000),1,0)
Q4 is =IF(AND(C2=”Man”,OR(E2=”Single”,E2=”Relationship”,E2=”Divorced”),F2=0),1,0)

1. ##### Francesca

=IF(AND(E2″married”;F2=0;C2=”man”);1;0)

1. ##### Francesca

sorry,
=IF(AND(E2″married”;F2=0;C2=”man”);1;0)

1. ##### Francesca

I don’t know why but in my formula, after E2, there are 2 signs minor and major as “different from”, and they are not shown in it

3. ##### Inbal

In question 4, you were looking for the unmarried , but you only wrote “single” in the formula, what about the rest of the categories? divorced, relationship ect……..?

4. ##### cska

Why don’t we use AND function in the case of question 3?