5 Best Ways to Use Excel’s IF Function with Multiple Conditions

The IF function in Excel is a potent tool that enables us to evaluate conditions and make crucial decisions by taking specific actions.

While the primary IF function evaluates a single condition, real-life situations often require working with multiple conditions.

This is where the IF function with multiple conditions becomes invaluable.  Learn the 5 best ways to use Excel’s IF Function with Multiple Conditions below!

In this article, we will dive into the following topics in detail –

Download the Excel Workbook below to follow along and understand how to use the IF function with multiple conditions–

download excel workbookIF-Function-with-Multiple-Condition.xlsx

 

Introduction to IF

The IF function is probably one of the most used Excel functions because it is easy to understand and very flexible when you apply it to real-life situations. It returns a value that you set if a condition is met, and a value if it is not met. The syntax of the IF function is –

=IF(Logical Test, Value if True, Value if False)

What it means:

=IF(The condition to be checked, Value to be shown if the condition is met, Value to be shown if the condition is not met)

In this example, our goal is to display a “Bonus” only when the sales amount surpasses $350, and if this condition is not satisfied, “No Bonus” will be shown. The following formula can be used to derive the result –

=IF(D2>350,”Bonus”,”No Bonus”)

• D2>350 – The condition that we are evaluating is that the value in cell D2 is greater than $350.
• Bonus – If the condition is true (i.e., the value in D2 is greater than 350), the function will return “Bonus”.
• No Bonus – If the condition is not true (i.e., the value in D2 is less than or equal to 350), the function will return “No Bonus”.

 

IF function with Two Conditions

We can combine the IF function with logical operators like AND and OR to be able to analyze multiple conditions and handle complex situations. AND operator is used when we want all the conditions to be fulfilled whereas OR operator is used when we want at least one of the conditions to be fulfilled.

The syntax for incorporating the AND and OR operator in an IF function is –

=IF(AND(condition1, condition2, …), value_if_true, value_if_false)

=IF(OR(condition1, condition2, …), value_if_true, value_if_false)

 

Example 1 – AND Operator

In this example, our goal is to display a “Bonus” only when the sales amount surpasses $350 and the department is IT, and if this condition is not satisfied, “No Bonus” will be shown. The following formula can be used to derive the result –

=IF(AND(D2>350,C2=”IT”),”Bonus”,”No Bonus”)

• D2>350 – The first condition that we are evaluating is that the value in cell D2 is greater than $350.
• C2=”IT” – The second condition is that the department mentioned in cell C2 is equal to IT.
• AND – It ensures that both conditions are fulfilled for the IF function to return TRUE.
• Bonus – If both conditions are fulfilled (i.e., the value in D2 is greater than 350 and the department is IT), the function will return “Bonus”.
• No Bonus – If any one of the two conditions is not fulfilled, the function will return “No Bonus”.

 

Example 2 – OR Operator

In this example, our goal is to display a “Bonus” when either region is South or the department is IT, and if this condition is not satisfied, “No Bonus” will be shown. The following formula can be used to derive the result –

=IF(OR(B2=”South”,C2=”IT”),”Bonus”,”No Bonus”)

• B2=”South” – The first condition that the region mentioned in cell B2 is South.
• C2=”IT” – The second condition is that the department mentioned in cell C2 is equal to IT.
• OR – It ensures that at least one of the conditions is fulfilled for the IF function to return TRUE.
• Bonus – If any one of the conditions is fulfilled (i.e., the region is South or the department is IT), the function will return “Bonus”.
• No Bonus – If both conditions are not fulfilled, the function will return “No Bonus”.

 

IFS – IF function with Multiple Conditions

When we are trying to test multiple conditions at once, Excel’s IFS formula provides an efficient solution. With the IFS formula, you can specify multiple conditions to check, and it will search for the first condition that is true. This eliminates the need for complex nested formulas.

The syntax for the IFS function is as follows:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],…)

IFS function will evaluate each logical test starting from the left and provide the value for the first condition that is true. Let’s look at an example to understand better. In this example, we need to calculate the bonus amount based on the sales amount provided.

• Sales Amount < 200, Bonus = 0
• Sales Amount < 350, Bonus = 10
• Sales Amount >=350, Bonus =15

 

STEP 1: We need to enter the IFS function in a blank cell:

=IFS(

STEP 2: Enter the first argument i.e. logical_test1. It is the first condition that we need to check.

=IFS(D2<200,

STEP 3: Enter the second argument i.e. value_if_true1. It is the value to return when the first condition is met.

=IFS(D2<200,0,

STEP 4: Enter the third argument i.e. logical_test2. It is the second condition that we need to check.

=IFS(D2<200,0,D2<350

STEP 5: Enter the fourth argument i.e. value_if_true2. It is the value to return when the second condition is met.

=IFS(D2<200,0,D2<350,10,

STEP 6: Enter the fifth argument i.e. logical_test3. It is the third condition that we need to check.

=IFS(D2<200,0,D2<350,10,D2>=350,

STEP 7: Enter the sixth argument i.e. value_if_true3. It is the value to return when the third condition is met.

=IFS(D2<200,0,D2<350,10,D2>=350,15)

STEP 8: Copy the formula down.

Click here to learn more about the IFS function in Excel.

 

Conclusion

In conclusion, the IF function in Excel is a versatile tool that allows for evaluating conditions and making decisions based on specific criteria. By incorporating multiple conditions using logical operators like AND and OR, complex scenarios can be handled effectively.

Additionally, the IFS formula provides a streamlined solution for evaluating IF function with multiple conditions without the need for nested formulas. By understanding and utilizing these functions, Excel users can enhance their data analysis capabilities and improve decision-making processes.