Excel If Statement with Multiple Conditions in Any Range

OR Type Multiple Conditions Excel IF Formula on a Range of Cells

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

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.

A Data set in Excel

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")

IF Function in Excel

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")

OR Type Multiple Conditions Excel IF Formula

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

Let’s break down the formula here.

  • OR(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000) returns TRUE if any one or both of the criteria are satisfied. Otherwise, it returns FALSE.
  • In this case, OR(AVERAGE(D4:D19)<25000,MIN(D4:D19)<20000 has returned 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")

OR Type Multiple Conditions Excel's IF Formula

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,"")

OR Type Multiple Conditions Excel IF Formula on a Range of Cells

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.

  • OR($D$4:$D$19<25000,$C$4:$C$19<DATE(2015,1,1)) checks each cell of the range 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")

AND Type Multiple Criteria Excel IF Function

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,"")

AND Type Multiple Criteria Excel IF Function on a Range of Cells

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")

Nested IF formula

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.

  • MIN(D4:D19)<20000 returns 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"))
  • AVERAGE(D4:D19)<25000 returns a 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")))

Nested IF Function

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo