Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Unlocking the Power of 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!

Watch our free training video on how to use IF Function with a Multiple Condition:

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 WORKBOOK

 

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)

See also  How to use the XLOOKUP function in Excel with 7 Examples!

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

Unlocking the Power of Excel's IF Function with Multiple Conditions

  • 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 –

See also  Find How Many Mondays in a Year in Excel with This Dates & Number Trick!

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

Unlocking the Power of Excel's IF Function with Multiple Conditions

  • 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”)

Unlocking the Power of Excel's IF Function with Multiple Conditions

  • 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”.
See also  Compare Two Columns in Excel for Matches & Differences: 3 Useful Methods

 

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(

Unlocking the Power of Excel's IF Function with Multiple Conditions

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

=IFS(D2<200,

Unlocking the Power of Excel's IF Function with Multiple Conditions

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,

Unlocking the Power of Excel's IF Function with Multiple Conditions

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

Unlocking the Power of Excel's IF Function with Multiple Conditions

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,

Unlocking the Power of Excel's IF Function with Multiple Conditions

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

See also  Return the Last Value in a Column with the Offset Function

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

Unlocking the Power of Excel's IF Function with Multiple Conditions

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)

Unlocking the Power of Excel's IF Function with Multiple Conditions

STEP 8: Copy the formula down.

Unlocking the Power of Excel's IF Function with Multiple Conditions

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.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!