14.2 C
London
Tuesday, May 28, 2024
HomeSoftware TutorialsExcelExcel: How to Use IF Function with Multiple Conditions

Excel: How to Use IF Function with Multiple Conditions

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

You can use the following formulas to create an IF function with multiple conditions in Excel:

Method 1: Use IF Function to Test if Multiple Conditions are Met

=IF(AND(B2="Guard",C2>20),"Yes", "No")

For this formula, if cell B2 is equal to “Guard” and cell C2 is greater than 20, then the function returns “Yes.” Otherwise it returns “No.”

Method 2: Use IF Function to Test if One of Several Conditions are Met

=IF(OR(B2="Guard",C2>20),"Yes", "No")

For this formula, if cell B2 is equal to “Guard” or cell C2 is greater than 20, then the function returns “Yes.” Otherwise it returns “No.”

The following examples show how to use each formula in practice with the following dataset in Excel:

Example 1: Use IF Function to Test if Multiple Conditions are Met

We can type the following formula into cell D2 to return “Yes” if the value in the Position column is “Guard” and the value in the Points column is greater than 20:

=IF(AND(B2="Guard",C2>20),"Yes", "No")

We can then click and drag this formula down to each remaining cell in column D:

Excel IF function multiple conditions using AND logic

The formula returns “Yes” for each row where the value in the Position column is “Guard” and the value in the Points column is greater than 20.

If both of these conditions aren’t met in a given row, then the formula returns “No” instead.

Example 2: Use IF Function to Test if One of Several Conditions are Met

We can type the following formula into cell D2 to return “Yes” if the value in the Position column is “Guard” or the value in the Points column is greater than 20:

=IF(OR(B2="Guard",C2>20),"Yes", "No")

We can then click and drag this formula down to each remaining cell in column D:

The formula returns “Yes” for each row where the value in the Position column is “Guard” or the value in the Points column is greater than 20.

If neither of these conditions are met in a given row, then the formula returns “No” instead.

Note that in both examples, we checked if two conditions were met.

However, you can place as many conditions as you’d like in the AND() or OR() functions depending on how many conditions you’d like to check.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Create IF Function to Return Yes or No
Excel: How to Use an IF Function with Range of Values
Excel: How to Use a MULTIPLY IF Function

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories