We all know the classic IF statement in Microsoft Excel but how do you use it with multiple conditions?
Let start with a quick refresher regarding the IF function in Excel. This function includes 2 mandatory arguments and 1 optional (value_if_false).
The IF syntax is: =IF(logical_test, value_if_true, [value_if_false]).
logical_test – the condition you want to test.
value_if_true – the returned value when the result of logical_test is TRUE.
value_if_false – the returned value when the result of logical_test is FALSE.
The IF function is one of many logical functions you can use in Excel; other logical functions are XOR, AND, OR, etc.
Check our video to learn how to use the IF statement formula in Excel!
In this article, we’ll share plenty of examples of IF function for 1, 2 and 3 conditions. Ready to learn?
Excel IF statement: how to use it with 1 condition
To use the IF function in Excel, simply follow the syntax =IF(logical_test, value_if_true, [value_if_false]).
Below are 2 examples using the IF formula:
Example 1 – IF statement with 1 condition
We want to send a promotional code to customers who bought more than 5 times with us. The formula we use is =IF(B42>5,”Send promo code”,”No”).
Note that if you use text values in the IF formula, you need to wrap them with quotation marks (“”) except for FALSE or TRUE.
Example 2 – IF function in Excel with 1 condition
We want to send a promotional code to customers who did not order this year. The formula we use is =IF(B42=0,TRUE,FALSE).
We don’t need to wrap TRUE or FALSE within quotation marks but we do need to do it for other text values.
To use the IF statement with multiple conditions, you can combine IF with AND or IF with OR.
Excel IF statement: how to use it with 2 conditions
In Excel, use the IF function with 2 conditions by adding AND or OR functions, for example, to it. In the video above we combined IF and OR statements.
Example – IF and OR with 2 conditions
In this example, we want to send a promotional code to customers who made at least 1 purchase in Q1 or in Q2. We decide to combine Excel IF and OR statements.
As a reminder, the OR syntax is =OR(logical1, [logical2], …).
Here, only David did not purchase anything in Q1 or Q2.
If you want to use the IF function in Excel, combining 2 conditions, check how just below!
Example – IF and AND with 2 conditions
In this example, we want to send a promotional code to customers who made at least 1 purchase in Q1 and in Q2. We decide to combine Excel IF and AND statements.
As a reminder, the AND syntax is =AND(logical1, [logical2], …).
The AND statement is underlined in yellow.
To use the IF statement in MS Excel with more conditions, follow the same process. More examples, with 3 conditions this time, just below!
Excel IF statement: how to use it for 3 conditions
Combine the IF function with AND or OR, for example, to use it with multiple conditions. Learn how by checking the 3 examples below!
Example – IF and AND for 3 conditions
We want to find customers who made at least 1 purchase in Q1 and in Q2, and who are subscribers to our Youtube channel.
As a reminder, the AND syntax in Excel is =AND(logical1, [logical2], …).
The formula we use is =IF(AND(B42>0,C42>0,D42=TRUE),”Send a code”,”No”).
Note that text values have to be wrapped with quotation marks for the system to recognise the values. For TRUE or FALSE, you may not use quotation marks.
Example – IF and OR with 3 conditions
We want to send a promo code to customers who made at least 1 purchase in Q1 or in Q2, or who are subscribers to our Youtube channel.
As a reminder, in Excel the OR function syntax is =OR(logical1, [logical2], …).
All our customers meet at least 1 criteria.
Example – IF, OR and AND with 3 conditions
Now, we want to send a promotional code to customers who made at least 1 purchase in Q1 or in Q2, and who are subscribers to our Youtube channel.
As a reminder, in Excel the OR function syntax is =OR(logical1, [logical2], …) and the AND syntax is =AND(logical1, [logical2], …).
We are combining the IF, OR and AND functions here. The formula we use is:
–The IF function is highlighted in bold.
-Highlighted in yellow, find the OR formula.
-In grey is the AND function.
-We highlighted the combining elements between AND and OR statements in green.
Follow the same logic to use IF statements with more than 3 conditions.
Now, you know how to use Excel IF statement with 3 conditions!
Join our Excel Course today and add a new certificate to your CV and LinkedIn profile!
