How to use IF function with 3 conditions

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 in Excel, 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 IF 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 IF 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 statement with multiple conditions

The final section of this article is focused on how to write an Excel IF statement with multiple conditions, 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 conditions are met:

  • 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

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

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.

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 if you write an IF formula with multiple criteria daily, your productivity will eventually skyrocket.

This is why, if you have any questions on how to use IF function with 3 conditions, 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

My name is Radu Meghes, and I'm the owner of excelexplained.com. 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 excelexplained.com 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?

  • Hello Sir.

    If you don’t mind? I’m trying to figure out what is the correct IF formula for this conditions but still can’t get the correct result. The IF statement that I’m doing are using date values. To explain in sentence it should be like this.

    “if inquired date is greater than date value 1/1/2021, “COUNTER”,If inquired date<confirmed date, "PRODUCTION", if confirmed date<arrived date, "ARRIVED"".

    I hope you can help me with this one. Thank you.

    • Hi Doe,

      What doesn’t work in your formula? You can try this one, but keep in mind that the formula will return the first result that is TRUE and ignore the rest of the formula:
      =IF(A1>DATE(2021, 1, 1), “COUNTER”, IF(A1 < B1, "PRODUCTION", IF(B1 < C1, "ARRIVED"))) where: A1 is the inquired date, B1 is the confirmed date, C1 is the arrived date.

      • Hi Sir. I already applied the formula you suggested. Counter and Production appears in the formula but for the Arrived does not. What I’m trying to do is whenever I input a date in inquired date, confirmed date and date arrived column, it will automatically fill counter, production and arrived.

      • That is probably because one of the two initial conditions is also TRUE. You need to play with the order of the conditions to achieve the desired result. If the inquired date > 1/1/2021, then this formula will always return COUNTER. If the confirmed date > inquired date the formula will always return PRODUCTION. The formula can only return one result, so if you need to display all the 3 strings, then a more complex formula is needed. The simple solution would be to have 3 different cells that check each condition and only return one result.

  • I am to say if cell B5 = Yes, then show 0, if B5 = Work Required then show 1, If B5 = No then show 3

    I have tried =IF(B5=”Yes”, 0, IF(B5=”Work Required”, 1, IF(B5=”No”, 3))) is this correct? But the error message’too many arguments entered’.

    What is the correct formula please

    • Hi Jon,

      For me, the formula works fine. Try to copy the one from below. Maybe you have a typo or something.

      =IF(B5=”Yes”, 0, IF(B5=”Work Required”, 1, IF(B5=”No”, 3)))

  • I am trying to have let’s say cell D2 look at cell B2 and if cell B2 is greater than or equal to 75, then in cell D2 I want the value of B2 to list three times. Example: B2 is 87, then in D2 I want to see “87 87 87”. How is this done?

    • Hi John,

      Try this formula:

      =IF(B2>=75, B2&” “&B2&” “&B2, “message when false”)

      Adjust the last part of the formula with the message you want to display when the condition is not met.

  • I am trying to create a formula that will allow me to type one of three text terms in and have a second cell return one of 3 values dependant on the text value I type in the first.

    If A= FD then B=10
    If A= AM then B=5
    If A= PM then B

    Any ideas?

    • Hi Jimbob,

      Use this formula on column B and change the reference to cell A1 to the cell in your file.

      =IF(A1=”FD”, 10, IF(A1=”AM”, 5, IF(A1=”PM”, 0)))

      I saw that your third condition was incomplete so I assumed you want to return 0. If not, just change it to whatever you need. Also, keep in mind that if A1 is not equal to FD, AM, or PM, the formula will return FALSE because it does not have a default value_if_false argument. If you need one, just add it after the zero (in the last IF statement).

      All the best,
      Radu

  • Hello, I am trying to type in the IF functions with the following conditions
    If A1 = 3 and A5 is greater than 149, then Yes, if not, then No
    If A1 = 2 and A5 is greater than 99, then Yes, if not, then No
    If A1 = 1 and A5 is greater than 49, then Yes, if not, then No

    How do I put this into one formula and apply to the remaining cells.

    Thank you.

  • Hi everyone. I have a lot of unanswered questions from the time I got COVID and I am trying to catch up. I will reply to all the comments, but please have a bit of patience.

  • Hi,

    I am working to avoid typing the price of my items in Excel each time I put in my sales data.
    Is there a way to use IF function just by typing the name of the item and then the price will appear by using if formula?

    Many thanks

    • Hi Michael,

      Yes, you can do that, but a better option would be to create a table with your items and prices and retrive the price using VLOOKUP or XLOOKUP.

  • Hello! I am trying to write a formula with 3 conditions. Basically I need the following.

    If E11=C11,F11*.05, otherwise E11*.01

    Can you help?

    • Hi Keith,

      The way I see it, there is only one condition to check, so there is no need for a nested IF formula. Just use this:

      =IF(E11=C11, F11*0.05, E11*0.01)

  • HI!

    I am working on an excel file which I want to add the number of items our employees use but I only want to add the same items so I could track how many per items were used. how can I do that? please help me. Thank you in advance! note: each employee used different items at the same time.

    • Hi Rome,

      I think the easiest solution would be a COUNTIF formula, where you count each time a tool shows up in your list.

      • thank you so much sir! you’re really a great help! please don’t get tired of helping other people like ne who are not that good in excel. again, thank you so much!

  • I have a 3 fields with words in them, that I need all to Match to return Ture and if any 1 of the 3 fields doesn’t match the other 2, I need it to flag, with a certain word. I feel like I needed a nest IF statement with AND, but I’m not getting what I need.

    • Hi Marissa,

      Sorry for the late reply, but I got COVID and didn’t manage to reply to comments for nearly 2 weeks.

      Do you need custom messages depending on which field doesn’t match? If not, then you don’t need a nested IF formula. Just add the conditions with an AND function and use the word in the value-if-false argument.

      Let me know if you need multiple custom messages.

  • Hi,
    I am trying to create a formula with 3 conditions but keep getting stuck – formula below, is anyone able to help?

    =IF(AND(E6 <-20%, F6 <-20%, G690%, I6 >100), “Yes”, IF(AND((G6 >-20%, G650%, H630, I6 <100), "No", "Red Flag")))

    Thanks!

    • Hi Tay,

      I am not sure if your formula got trimmed by the security system, but I see some parts which I don’t understand. FOr example, you have G690% as a condition in the first AND statement, and G650%, H630 in the second AND statement. What are the conditions you are trying to check?

  • I need to return a number dependent on three different criteria:

    if the year = 2017 return a number
    if the year is greater than 2017 return a number
    if the year is = to 2022 return a number

    I have a combo of 3 if statements – the first two are working, but I can’t get the third to return the appropriate number. The third if is returning the same result as the second statement

    =IF(K$4=$E5, ($C5*$I5), IF(K$4 > $E5, ($I5*12), IF(K$4 < ($F5+1), ($I5*$D5),0)))

    • Hi Channele,

      That’s because you need to change the order of your IF statemements. Reverse the third condition with the second one and your formula should work. What you need to remember is that IF formulas return the result of the first condition that is TRUE, and ignore the rest.

      In your example, the second condition is checking if a number is greater than 2017, while your third checks if the number is 2022. Because 2022 is greater than 2017, the second condition returns TRUE and the third one is never checked.

  • Hi,

    I have 3 columns with amounts in them. I want to say “if A1 has an amount, return “FSA”, if B1 has an amount, return “DCA”, if C1 has an amount, return “PNC”

    So far I have the equation =IF(A1″”,”FSA”,””) and similar equations for the other codes in individual columns, but I need this to be one equation to return the code in only one column. Can you please help with how I should nest this? Much appreciated!!!

    • Hi Kristin,

      You can use this:

      =IF(A1=1, “FSA”, IF(B1=2, “DCA”, IF(C1=3, “PNC”, “Custom message”)))

      Just replace 1, 2, and 3 with your desired values or cells and adjust the custom message when neither of the conditions is TRUE. Also, keep in mind that if the first condition is TRUE, the second and third conditions are not checked anymore and the first string (“FSA”) is displayed.

      I hope this helps.

  • Hello,
    I have 3 date columns I’m trying to compare:
    actual (a), first (b) and plan date (c)

    if actual is first or plan date “late”
    If actual is = first or plan date “on time”

    I’ve tried IF(and) statements, but keep receiving errors.

    Thank you in advance,

    • Hi Melissa,

      I am not sure this is the formula you are looking for, but here goes:

      =IF(AND(A1=B1, C1=”late”), “Late”, IF(AND(A1=B1, C1=”on time”), “On time”, “Default message”))

      Let me know if it works as intended.

  • Please help me with this example – I have 10 employees who I need to track voluntary and mandatory overtime on monthly. I have a google sheet set up that column “c” will have a “v” if voluntary and column “d” will have a “m” if mandatory. I then need the formula to take the hours from column “b” and keep a running total under both voluntary and mandatory for each employee. Is this to much to ask for google sheets to do?

    • Hi Andrea,

      Definitely not. Google Sheets is capable of doing a lot of things and this task is easily achievable. What I would use is either a SUMIF formula that changes the reference of the range as you copy the formula down (like this $C$1:C10) so that it keeps adding hours as you drag the formula downwards or a simple formula that adds the previous total with the result of an IF statement.

      Both are achievable in Google Sheets.

      • Hi Radu,

        I am trying to categorise areas (into urban, suburban and rural) according to population density and number of inhabitants (I have thus a 3 x 3 matrix to categorise the results). My column E tells me the number of inhabitants, while my column F tells me the area in square KMs.

        The following column (G) tells me the population density (# of inhabitants/area in sq. KMs).

        Now the population density value (column G) helps me categorise the areas according to Urban, suburban or rural.

        However, I am trying to use an IF or IFS formula in a new column (H) to consolidate the results obtained in column E, since a Minimum number of inhabitants per area is required to be considered an Urban (to avoid that a small area with a high-density population falls into the urban area) or Suburban area.

        My current formula does not work. But this is what I got so far:
        =IFS(G4<250; "Rural"; G4=750; “Urban”)&IFS(E4< 5000; "Rural"; E440000; “Urban”)

        What am I doing wrong?

        Thanks!

        How should I proceed?

      • Hi Pablo,

        Can you post the matrix? I know you can’t post a table in the comment, but it’s an easy matrix I would say. Alternatively, you can contact me via email and I will help you out.

        Thanks,
        Radu

  • >