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

### OR function

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

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:

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:

## 8 comments

Skip to comment form ↓

## Anonymous

02/17/2015 at 10:01 am (UTC 0) Link to this comment

where is the table i c’t find anyone

## Frédéric LE GUEN

02/17/2015 at 11:48 am (UTC 0) Link to this comment

What is your question?

## ChrisB

10/15/2014 at 10:25 am (UTC 0) Link to this comment

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)

## Francesca

05/02/2015 at 7:44 am (UTC 0) Link to this comment

What about this Q4, a little shorter

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

## Francesca

05/02/2015 at 7:45 am (UTC 0) Link to this comment

sorry,

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

## Francesca

05/02/2015 at 7:49 am (UTC 0) Link to this comment

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

## Inbal

12/30/2013 at 10:08 am (UTC 0) Link to this comment

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……..?

## cska

10/18/2013 at 11:40 am (UTC 0) Link to this comment

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