Coming from a SQL programming background you might find it difficult trying to adapt the AND / OR operators to your Excel formulas, so here is a method that I use to solve this problem.

The Formulas

To use the AND operator use the following format:

= IF( AND( logical_1, logical_2, ... ), TRUE, FALSE )

To use the OR operator use the following format:

= IF( OR( logical_1, logical_2, ... ), TRUE, FALSE )

Simple huh? Lets put this to practice!

Example #1 - Using AND

I have the following data set which shows the amount of new and cancelled customers by Country.

I will create a new column called Target and I want this column to follow these conditions:

  1. New customers has to be greater than 5
    AND
  2. Cancel customers has to be less than 2

In my new column I enter the following formula :

= IF( AND( B4 > 5, C4 < 2 ), "Pass Target", "Failed Target" )

As a screen shot:

After copying this formula down for the rest of the Target column:

In SQL terms the formula would look like this:

SELECT * FROM table WHERE NEW > 5 AND CANCEL < 2

You can also add as many conditions to the AND() formula.

Example #2 - Using OR

We will use the same data set from above in this example and replace the AND operator with OR.

In the Target column I want the condition to be :

  1. New customers has to be greater than 5
    OR
  2. Cancel customers has to be less than 2

In my new column I enter the following formula :

= IF( OR( B4 > 5, C4 < 2 ), "Pass Target", "Failed Target" )

As a screen shot:

After copying this formula down for the rest of the Target column:

In SQL terms the formula would look like this:

SELECT * FROM table WHERE NEW > 5 OR CANCEL < 2

You can also add as many conditions to the OR() formula.

Download this example as an Excel Workbook

If you have any other ways of approaching this leave a comment!

Enjoy!