I received a lot of questions on how to use IF function with 3 conditions, so I've decided to write an article on this topic.
The IF examples described in this article assume that you have a basic understanding of how the IF function works. All examples from this article work in Excel for Microsoft 365 or Excel 2019, 2016, 2013, 2010, and 2007.
If you are unfamiliar with the IF function, then I strongly recommend reading my article on Excel IF function first. It's a step by step guide, and it includes a lot of useful examples. Make sure you also download the exercise file.
As a data analyst, you need to be able to evaluate multiple conditions at the same time and perform an action or display certain values when the logical tests are TRUE. This means that you will need to learn how to write more complex formulas, which sooner or later will include Excel IF statements with multiple conditions.
Let's take a look at how to write a simple IF function with 3 logical tests.
The first example uses an IF statement with three OR conditions. We will use an IF formula which sets the Finance division name if the department is Accounting, Financial Reporting, or Planning & Budgeting.
The IF statement from cell E31 is:
=IF(OR(D31="Accounting", D31="Financial Reporting", D31="Planning & Budgeting"), "Finance", "Other")
This IF formula works by checking three OR conditions:
Accounting? In our case, the answer is no, and the formula continues and evaluates the second condition.
Financial Reporting? The answer is still no, and the formula continues and evaluates the third condition.
Planning & Reporting? The answer is yes, our IF function returns TRUE, and displays the word Finance in cell E31.
Next, we focus our attention on an example that uses an IF statement with three AND conditions.
Our table shows exam scores for three exams. If the student received a score of at least 70 for all three exams, then we will return Pass. Otherwise, we will display Fail.
The IF statement from cell H53 is:
=IF(AND(E53>=70, F53>=70, G53>=70), "Pass", "Fail")
This IF formula works by checking all three AND conditions:
higher than or equal to 70? In our case, the answer is yes, and the formula continues and evaluates the second condition.
higher than or equal to 70? Well, yes it is. Now the formula moves to the third condition.
The final section of this article is focused on how to write an Excel IF statement with multiple conditions, and it includes two examples:
In this example, I have calculated the grade of the students based on their scores using a formula with 4 nested IF functions.
=IF(E107<60, "F", IF(E107<70, "D", IF(E107<80, "C", IF(E107<90, "B" ,"A"))))
Note: In this case, the order of the conditions influences the result of your formula. When your conditions overlap, Excel will return the [value_if_true] argument from the first IF statement that is TRUE and ignores the rest of the values. If you want your formula to work properly, always pay attention to the logical flow and the order of your nested IF functions.
Note: If you have Office 365 installed, then you can also use the new IFS function. You can read more about IFS on Microsoft’s website.
I have saved the best for last. This example is the most advanced from this article, as it involves an IF statement with several other logical functions.
In the exercise file, I have included a list of orders. Each row includes the order date, the order value, the product category, and the free shipping flag. We want to flag orders as eligible if the following cumulative logical conditions are met:
The formula I've used for cell H80 is shown below:
=IF(AND(D80>=DATE(2020,1,1), D80<=DATE(2020,12,31), OR(F80="PC", F80="Laptop"), NOT(G80="Yes")), "Eligible", "Not eligible")
Here's how this works:
ANDmakes sure that all the logical conditions need to be met to flag the order as Eligible. If any of them is FALSE, then our entire IF statement will return the [value_if_false] argument.
D80<=DATE(2020,12,31)check if the order was placed between January 1st and December 31st, 2020.
ORis used to check whether the product category is
NOTis used to check if the Free shipping flag is different from
And there you have it. I hope that after reading this guide, you have a much better understanding of using IF function with 3 logical tests (or any number actually). While it may seem intimidating at first, I guarantee that once you start using these functions daily, your productivity will skyrocket.
If you struggle to write more complex formulas, then I strongly suggest investing in an Excel course. Mynda Treacy from myonlinetraininghub.com has an excellent collection of courses, ranging from Beginner to Specialist.
If you have any questions on how to use IF function with 3 conditions, then please leave a comment below and I will do my best to help you out. I reply to every comment or email that I receive.