How to use IF function with 3 conditions

March 18, 2022

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 2021, 2019, 2016, 2013, 2010, and 2007.

IF is one of the most used Excel functions. In case 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. It also shows the basics of writing an Excel IF statement with multiple conditions, but it's not as detailed as this guide. 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 multiple IF statements (also known as a nested if statement), nested one inside the other.

Let's take a look at how to write a simple IF function with 3 logical tests.

How to use IF function with 3 conditions

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")

IF function with 3 conditions

This Excel formula works by checking three OR conditions:

  • Is the data from the cell D31 equal to Accounting? In our case, the answer is no, and the formula continues and evaluates the second condition.
  • Is the text from the cell D31 equal to Financial Reporting? The answer is still no, and the formula continues and evaluates the third condition.
  • Is the text from the cell D31 equal to 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")

IF function with 3 logical tests

This above formula works by checking all three AND conditions:

  • Is the score for Exam 1 higher than or equal to 70? In our case, the answer is yes, and the formula continues and evaluates the second condition.
  • Is the score for Exam 2 higher than or equal to 70? Well, yes it is. Now the formula moves to the third condition.
  • Is the score for Exam 3 higher than or equal to 70? Yes, it is. Since all three conditions are met, the IF statement is TRUE and returns the word Pass in cell H53.

Excel IF function with multiple conditions

The final section of this article is focused on how to write an Excel IF statement with multiple conditions (also know as a nested IF function), and it includes two examples:

  • multiple nested IF statements (also known as nested IFS)
  • formula with a mix of AND, OR, and NOT conditions

How many IF statements can you nest in Excel? The answer is 64, but I've never seen a formula that uses that many. Also, I'm sure that there are far better alternatives to using such a complicated formula.

Multiple nested IF statements

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.

If you have to write an IF statement with 3 outcomes, then you only need to use one nested IF function. The first IF statement will handle the first outcome, while the second one will return the second and the third possible outcomes.

4 nested IF statements

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.

Multiple IF statements in Excel with AND, OR, and NOT conditions

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 tests are true:

  • the order was placed during 2020
  • the order includes products from only two categories: PC or Laptop
  • the order was not flagged as Free shipping

I've used the below formula for cell H80:

=IF(AND(D80>=DATE(2020,1,1), D80<=DATE(2020,12,31), OR(F80="PC", F80="Laptop"), NOT(G80="Yes")), "Eligible", "Not eligible")

IF statement with AND, OR, and NOT functions

Here's how this works:

  1. AND makes 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.
  2. D80>=DATE(2020,1,1) and D80<=DATE(2020,12,31) check if the order was placed between January 1st and December 31st, 2020.
  3. OR is used to check whether the product category is PC or Laptop.
  4. Finally, NOT is used to check if the Free shipping flag is different from Yes.

I've also added a video that shows how to nest IF functions in case you are still having difficulties understanding how a nested IF formula works.

What to do next?

Conditional functions serve as powerful data analysis tools. That is why I recommend spending some time understanding how they work. You can use these examples as a starting point to write more complex formulas that test multiple conditions.

I hope that after reading this guide, you have a much better understanding of using IF function with 3 logical tests. While it may seem intimidating at first, I guarantee that if you write an IF formula with multiple criteria daily, your productivity will eventually skyrocket.

If you have any questions on how to use IF function with 3 conditions (or other Excel functions), 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.

About me
Radu from ExcelExplained

My name is Radu Meghes, and I'm the owner of Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

  • Iko says:

    Hi, could you help me with this formula for attendance point.

    - This condition apply for all employees. Late for 10 mins minus 1 point, late for 20 mins minus 2 points
    - However, 5 particular employees' name needs different calculations. Late for 10 mins minus 2 points, late for 20 mins minus 3 points.

    As the attendance report combine all employees' name.
    Could we do this in 1 column 1 formula, instead of take out the 5 names in different column and do 2 sets of formulas?

    Thank you and appreciate your help.

    • Radu says:

      Hi Iko,

      Thanks for reaching out. There are better solutions than this one, but if you want an IF formula you can use this:

      =IF(OR(A1="name1", A1="name2", A1="name3", A1="name4", A1="name5"), IF(B1=20, -3, IF(B1=10, -2, "")), IF(B1=20, -2, IF(B1=10, -1, "")))

      I hope this works as intended.

      Let me know if you need additional help.

      • Iko says:

        Hi, Radu

        Thank you so much. The formula works perfectly.
        Wondering though what better solutions to this.
        Could you let me know, please? I will dig deeper.
        Thanks again

      • Radu says:

        Glad to hear it worked.

        I prefer not to hard code names or variables directly into formulas. I prefer to store them on separate tables and reference them when needed. You can then do lookups or fancy formulas by simply referencing the table, which keeps things organized. And if you decide to add more people to the list of exclusion, you insert another row in the table, and the formula changes automatically without manual intervention because the table reference is not changed.

  • Elizaveta says:

    Good afternoon,

    I would appreciate your help in creating a formula for following numbers, but my excel only read it as True or False.

    BAR3 295 350 450 495 750 595

    Thank you in advance

  • Essential Functions

    Popular Articles

    Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

    Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
    Copyright © 2017-2022