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:
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
Hi,
I’m trying to remove FALSE from the cell with multiple if conditions if the criteria isn’t met. So in the example below =IF(D3=P2,E3+F3+G3,””) cell P3 correctly returns the sum of cells E3+F3+G3 if cell P2 is the same value as cell D3 and a apparently blank cell if not.
Similarly cell P3 returns the sum of cells =IF(ISNUMBER(SEARCH(“water”, B3)),E3+F3+G3, “”) of cells E3+F3+G3 if cell B3 contains water and a apparently blank cell if not.
However, I can’t seem to combine the two statements without getting an formula error message or returning a FALSE statement in the cell, e.g. =IF(ISNUMBER(SEARCH(“water”,B3)),IF(D3=P2,E3+F3+G3,” “)) cell P3 correctly returns the sum of cells of E3+F3+G3, if P2 is the same value as D3 and B3 contains the word water, but it returns FALSE rather than appearing to leave the cell blank if B3 doesn’t contain water.
I’d like the cell to appear blank in the second example in a similar manner to the first two if possible, but not sure how to go about doing so.
Any pointers would be gratefully received, but I can always post this on a forum if it’s a bit outside your remit. And just to emphasise that I only require the cell to appear blank rather than actually be blank.
Many thanks, Trevor
Hi Trevor…Please share what you have built already and the results you have. That will better enable us to help work with you toward a solution.