How to use if function in excel with multiple conditions (multiple criteria).In summary, there can be 2 basic types of multiple conditions - with AND and OR logic. Consequently, your IF function should embed an AND or OR function in the logical test, respectively.
AND function. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.
OR function. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met; FALSE otherwise.
To illustrate the point better, let's have a look at a few IF examples with multiple conditions.
Example 1. Using IF & AND function in Excel
Suppose, you have a table with the results of two exam scores. The first score, stored in column C, must be equal to or greater than 20. The second score, listed in column D, must be equal to or exceed 30. Only when both of the above conditions are met, a student passes the final exam.
The easiest way to make a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function:
Condition: AND(B2>=20, C2>=30)
Easy, isn't it? The formula tells Excel to return "Pass" if a value in column C >=20 AND a value in column D >=30. Otherwise, the formula returns "Fail". The screenshot below proves that our Excel IF /AND function is correct:
Note. Microsoft Excel checks all conditions in the AND function, even if one of the already tested conditions evaluates to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.
Example 2. Using IF with OR function in Excel
You use the combination of IF & OR functions in a similar way. The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met.
So, if we modify the above formula in the following way:
Column E will have the "Pass" mark if either the first score is equal to or greater than 20 OR the second score is equal to or greater than 30.
As you see in the screenshot below, our students have a better chance to pass the final exam with such conditions (Scott being particularly unlucky failing by just 1 point : )
Naturally, you are not limited to using only two AND/OR functions in your Excel IF formulas. You can use as many logical functions as your business logic requires, provided that:
- Excel 2013, 2010 and 2007, your formula includes no more than 255 arguments, and the total length of the formula does not exceed 8,192 characters.
- In Excel 2003 and lower, you can use up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.
Example 3. Using IF with AND & OR functions
In case you have to evaluate your data based on several sets of multiple conditions, you will have to employ both AND & OR functions at a time.
In the above table, suppose you have the following criteria to evaluate the students' success:
- Condition 1: column C>=20 and column D>=25
- Condition 2: column C>=15 and column D>=20
If either of the above conditions is met, the final exam is deemed passed, otherwise - failed.
The formula might seem tricky, but in a moment, you will see that it is not! You just have to express two conditions as AND statements and enclose them in the OR function since you do not require both conditions to be met, either will suffice:
Finally, use the above OR function as the logical test in the IF function and supply value_if_true and value_if_false arguments. As the result, you will get the following IF formula with multiple AND / OR conditions:
The screenshot below indicates that we've got the formula right:
AND function. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.
OR function. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met; FALSE otherwise.
To illustrate the point better, let's have a look at a few IF examples with multiple conditions.
Example 1. Using IF & AND function in Excel
Suppose, you have a table with the results of two exam scores. The first score, stored in column C, must be equal to or greater than 20. The second score, listed in column D, must be equal to or exceed 30. Only when both of the above conditions are met, a student passes the final exam.
The easiest way to make a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function:
Condition: AND(B2>=20, C2>=30)
IF/AND formula: =IF((AND(C2>=20, D2>=30)), "Pass", "Fail")
Easy, isn't it? The formula tells Excel to return "Pass" if a value in column C >=20 AND a value in column D >=30. Otherwise, the formula returns "Fail". The screenshot below proves that our Excel IF /AND function is correct:
Note. Microsoft Excel checks all conditions in the AND function, even if one of the already tested conditions evaluates to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.
Example 2. Using IF with OR function in Excel
You use the combination of IF & OR functions in a similar way. The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met.
So, if we modify the above formula in the following way:
=IF((OR(C2>=20, D2>=30)), "Pass", "Fail")
Column E will have the "Pass" mark if either the first score is equal to or greater than 20 OR the second score is equal to or greater than 30.
As you see in the screenshot below, our students have a better chance to pass the final exam with such conditions (Scott being particularly unlucky failing by just 1 point : )
Naturally, you are not limited to using only two AND/OR functions in your Excel IF formulas. You can use as many logical functions as your business logic requires, provided that:
- Excel 2013, 2010 and 2007, your formula includes no more than 255 arguments, and the total length of the formula does not exceed 8,192 characters.
- In Excel 2003 and lower, you can use up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.
Example 3. Using IF with AND & OR functions
In case you have to evaluate your data based on several sets of multiple conditions, you will have to employ both AND & OR functions at a time.
In the above table, suppose you have the following criteria to evaluate the students' success:
- Condition 1: column C>=20 and column D>=25
- Condition 2: column C>=15 and column D>=20
If either of the above conditions is met, the final exam is deemed passed, otherwise - failed.
The formula might seem tricky, but in a moment, you will see that it is not! You just have to express two conditions as AND statements and enclose them in the OR function since you do not require both conditions to be met, either will suffice:
OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)
Finally, use the above OR function as the logical test in the IF function and supply value_if_true and value_if_false arguments. As the result, you will get the following IF formula with multiple AND / OR conditions:
=IF(OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)), "Pass", "Fail")
The screenshot below indicates that we've got the formula right:
0 comments:
Post a Comment