Last Time we looked at the basics of the Excel IF Statement that is based on one condition and one value if true or false, if you need a recap try here, or if you are ready to stride ahead, let’s get going. It’s time to use multiple conditions and multiple true/false values.

It’s always easier with an example. Open an new workbook and enter this data as below. In the example we will be looking sales peoples location, annual sales of widgets as well as the numbers of years that the sales people have been employed.

Now then let’s get on with turning up the volume on these IF statements. Let’s suppose that we want to assign a % bonus in column E based on two different criteria.

Lets give those who

1. Have been employed for more than 5 years AND sales of widgets more than 50,000 a 10% bonus

2 All others who don’t meet these criteria get  a 5% bonus

The formula looks like this:-

=IF(AND(C3>50000,D3>10),10,5)

To include two criteria in your formula, when both criteria must be met you need to nest an ‘AND’ function within your IF statement.  Then you can grab cell E3 by it’s fill handle and drag it down to E6.

The above formula uses a nested AND function for it’s logical test.  The AND function will decide if both conditions are true.  Excel will automatically look to the deepest nested function first then work its way out, so the first thing it will do if determine the result of the AND function it then moves onto the IF function and peforms it based on the results of the AND function.

Having fun?, let’s continue…

You can also use a nested OR function in the IF statement, for exmaple if we wanted to give a 10 % bonus to the employees if they in fact meet either condition.  In our exmaple they must have sales of over 50,000 widgets or have been an employee for more than 10 years.

The formula looks like this:-

=IF(OR(C3>50000,D3>10),10,5)

The formula can be be dragged down to fill the rest of your table.

You can easily use more then two criteria in nexted AND and OR functions, you just need to deperate them with commas.

Now, the above example is working great of there are just two bonus levels, but what if there are more in the company, what if we wanted a bonus of 15% of they met both criteria, 10% of they met either and 1% if they didn’t meet any?.

We need to nest one IF Statement within another, as always way easier with an example, the formula looks like this

=IF((AND(C3>50000,D3>10)),15,(IF((OR(C3>50000,D3>10)),5,1)))

Notice we have two different logical tests and two different values if true we still have only one value if false.  You can nest up to 7 different IF’s one one formula, so you can return 8 different results- that is 7 if TRUE and 1 if FALSE.

With the options of using 7 different arguments, you can do some very swanky forumulas, if you need more than 7 then you will need to use a LOOKUP function, i will cover these on another post.

Go and try this out…have fun.