Today I will be showing how you can use Excel’s **IF** function with multiple conditions in any range.

**Table of Contents**hide

**Download Practice Workbook**

**An Introduction to Excel’s IF Function**

Before going to the main discussion, let us introduce you to today’s data set. We have the employee record of a company named Mars Group.

We have the Employee Names, their Starting Dates, and Salaries in columns **B**, **C** and **D** respectively.

Now think for a moment, the chief of Mars Group wants to come to a decision.

If the average salary of his employees is less than $25000, he will increase the salary of each employee by $5000.

But the question is, how can he come to the decision?

The** IF** function of Excel will come in handy here. Just write this formula in any cell of your worksheet and see the result:

`=IF(AVERAGE(D4:D19)<25000,"Increase","Do not Increase")`

See, Excel’s **IF** Function has decided it for you.

It has first determined whether the average salary is less than $25000 or not.

When it saw the average salary is not less than $25000, it advised you not to increase the salary.

So we can see that the **IF** function takes three arguments:

- One criterion
- One output displayed if the criterion is satisfied
- One output displayed if the criterion is not satisfied (Optional. Default is
**“FALSE”**)

So in short, the **IF** function takes one criterion and two outputs. It returns the first output if the criterion is satisfied, and returns the second if the criterion is not satisfied.

And the syntax is:

`=IF(logical_test,value_if_true,[value_if_false])`

**Excel If Statement with Multiple Conditions in Any Range**

Now I hope you have understood how the** IF** function of Excel works with one condition.

Let’s try to work with multiple conditions.

**1. OR Type Multiple Conditions**

**1.1 Condition for a Single Value**

Let’s think for a moment, the chief of Mars Group has brought a bit of flexibility in his decision.

He will increase the salary of each employee if the average salary is less than $25000, or the lowest salary of the employees is less than $20000.

We see, there are two conditions here. But these are OR-type conditions. That means, the condition is satisfied if either one or both the conditions are satisfied.

Using an **IF** statement for this type of multiple conditions is quite easy. Just wrap the two conditions within an **OR** function of Excel.

The formula that we shall use here is:

`=IF(OR(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000),"Increase","Do not Increase")`

See, this time Excel has recommended us to increase the salary.

Let’s break down the formula here.

returns`OR(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000)`

**TRUE**if any one or both of the criteria are satisfied. Otherwise, it returns**FALSE.**- In this case,
has returned`OR(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000`

**TRUE**because the average salary is not less than $25000, but the lowest salary is less than $20000. - So the formula becomes:

**
**

`=IF(TRUE,"Increase","Do not Increase")`

- As it is
**TRUE**inside the**IF**function, it returns the first output, “**Increase**”.

Now, if you understand this, can you tell me what the formula will be to get a “Yes” if either the highest salary is greater than $40000 or the lowest salary is less than $20000, otherwise “No”?

Yes. You are right. The formula will be:

`=IF(OR(MAX(D4:D19)>40000,MIN(D4:D19)<20000),"Yes","No")`

**1.2 Conditions for a Range of Values**

Now consider a different scenario.

The chief of Mars Group has decided he will increase the salary of those employees whose present salaries are less than $25000, or who started their jobs before 1/1/2015.

But how can he identify those employees?

Pretty easy. In place of using just a single cell reference within the **IF** function, you can use a range of cell references inside the function.

See the below formula.

`=IF(OR($D$4:$D$19<25000,$C$4:$C$19<DATE(2015,1,1)),$B$4:$B$19,"")`

Here, I have inserted the formula in the first cell of the new column, cell **F4**.

And then dragged the **Fill Handle** through the rest of the cells.

It returned the names of all the employees whose salaries are less than $25000, or who started their jobs before January 01, 2015.

If you carefully notice, you will find that in lieu of inserting a single cell reference within the **IF** function, I have inserted a range of cell references (**$D$4:$D$19**) within the function.

Of course, you can do that. And it will check the criteria one by one for each cell of the range.

For better understanding, let’s break down the formula.

checks each cell of the range`OR($D$4:$D$19<25000,$C$4:$C$19<DATE(2015,1,1))`

**D4**to**D19**(**Salary**) and of the range**C4**to**C19 (Starting Date)**and returns a**TRUE**if either salary is less than $25000 or starting date is less than January 01, 2015.- So the formula becomes:

`=IF({TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE, FALSE, FALSE, FALSE, TRUE}, {B4,B5,B6,B7,B8,B9,B10,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19},{"","","","","","","","","","","","","","","",""})`

- For each
**TRUE**in the criteria range, it returns the content of the corresponding cell of column**B**, that means the employee name, and for each**FALSE**, it returns a blank cell. - We have used
**Absolute Cell Reference**here because we do not want to increase the cell references when we drag the**Fill Handle**.

Finally, you may want to remove the blank cells from the list, which means that you may just want a list of the employees whose salaries are to be increased.

Unfortunately, you can’t do this using only the **IF** function.

But obviously, there are ways. One way to accomplish this is using the **FILTER** function of Excel.

**2. AND Type Multiple Conditions**

**2.1 Condition for a Single Value**

If you understand the earlier section well, can you give an answer to another question?

What will be the formula if the chief of the company wants to increase the salary of each employee if the average salary is less than $25000 and the lowest salary is $20000?

Yes. You have guessed right. Just wrap the two conditions within an **AND** function in place of the **OR** function.

Just like this:

`=IF(AND(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000),"Increase","Do not Increase")`

See, this time Excel has suggested we not increase the salary because both the conditions, average salary less than $25000 and lowest salary less than $20000, are not satisfied. Only one condition is satisfied.

If you want to know more details about the formula, go to section 1.1.

**2.2 Conditions for a Range of Values**

The Chief of Mars Group is, in fact, a very confused man. This time he has taken another decision.

He will increase the salaries of only those employees who have a present salary of less than $20000 and started the job before January 01, 2017.

How can he find those employees?

Yes. You are right. Just use the formula of section 1.2 with an **AND** function instead of an **OR **function.

`=IF(AND($D$4:$D$19<25000,$C$4:$C$19<DATE(2017,1,1)),$B$4:$B$19,"")`

See, we have got the employees who satisfy both the conditions.

To know details about the formula, go to section 1.2

**3. Nested IF Formula**

In the previous section, we determined whether both the conditions, average salary less than $25000, and lowest salary less than $20000 are satisfied or not.

But have you ever thought that we can determine this in another way?

By using an **IF** within another **IF** function.

We shall first check whether the lowest salary is less than $20000 or not.

If not, it will return **“Do not Increase”**.

But if it is, then we will again check whether the average salary is less than $25000 or not.

If not, then it will return **“Do not Increase”**.

But if it is, this time it will return **“increase”**

So the complete formula will be:

`=IF(MIN(D4:D19)<20000,(IF(AVERAGE(D4:D19)<25000,"Increase","Do not Increase")),"Do not Increase")`

See, Excel has suggested us not to increase the salary, because both the conditions are not satisfied.

Let’s break down the formula for better understanding.

returns`MIN(D4:D19)<20000`

**TRUE**if the lowest salary is less than $20000, otherwise it returns**FALSE**. Here it returns**TRUE**.- So the formula becomes:

`=IF(TRUE,(IF(AVERAGE(D4:D19)<25000,"Increase","Do not Increase")),"Do not Increase")`

- As
**IF**sees a**TRUE**, it enters into the first output. That means it enters into`(IF(AVERAGE(D4:D19)<25000,"Increase","Do not Increase"))`

returns a`AVERAGE(D4:D19)<25000`

**TRUE**if the average salary is less than $25000, otherwise it returns**FALSE**. This time it returns**FALSE**.- So the formula becomes:

`=IF(TRUE,(IF(FALSE,"Increase","Do not Increase")),"Do not Increase")`

- So it returns the second output of the second
**IF**,**“Do not Increase”**.

Now if you understand this, let’s try to answer an old question differently.

Can you tell me what the formula will be to get a “Yes” if either the highest salary is greater than $40000 or the lowest salary is less than $20000, otherwise “No”?

Yes, you are right. The formula will be:

`=IF(MAX(D4:D19)>40000,"Yes",(IF(MIN(D4:D19)<20000,"Yes","No")))`

**Conclusion**

Using these methods, you can use any **IF** statements with multiple conditions of both **AND** types and **OR **types in Excel. Do you know any other method? Or do you have any questions? Feel free to inform us.