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.

Contents hide
1 How to use IF function with 3 conditions
2 Excel IF statement with multiple conditions
2.1 Multiple nested IF statements
2.2 Multiple IF statements in Excel with AND, OR, and NOT conditions

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.

IF function with 3 conditionsDownload

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?

  • Hi Radu,

    I am trying to use a formula ( using if And )for the below condition

    If And IF Then Else
    High 4 Late OK
    Medium 3 Late ok
    Low 2 Late ok

    How do I check all the if statements in a single cell. I am able to make it for one cell but unable to make it for all 3 conditions together

    Thanks

    Reply

    • Hi Ajay,

      I think the formula you are looking for is this:

      =IF(AND(A1=”High”, B1=4), “Late”, IF(AND(A1=”Med”, B1=3), “Late”, IF(AND(A1=”Low”, B1=2), “Late”, “ok”)))

      Let me know if this works as intended.

      Reply

  • Hi,

    Thank you for that. I would like to request help if possible.i have 3 columns. One is kgco2e the other are classification of flights I km travelled.i want to input an if function into the kgco2e column that will multiply the km travelled by a number depending on classification eg. For a short haul flight, I want to assign that classification a factor which would mean it the km travelled are 200, it would be 200*1.34= number to put in kgco2e

    Many thanks

    Reply

    • Hi Mohammed,

      How many possible types of flights do you have? Nesting IF statements is dependant on the number of possible outcomes. You have only provided one example, which is “a short haul flight.”

      Reply

  • Hi
    Request your help on this.

    I have 3 Columns – 1. Estimated Expenditure 2. Actual Expenditure 3. Remarks

    Let say Estimated Expenditure is X Amount.

    Conditions –

    1.I want Remarks to be “Under control” if the Actual expenditure is 0.5X.
    2.I want Remarks to be “Take Notice” if the Actual expenditure is between 0.5 x & 0.75X.
    3.I want Remarks to be “Initiate Cut down” if the Actual expenditure is between 0.75X & x.
    4.I want Remarks to be “Unacceptable” if the Actual expenditure is above X.

    Regards

    Reply

    • Hi Neil,

      The formula you are looking for is:

      =IF(A2<0.5*B2, "Under control", IF(A2<0.75*B2, "Take notice", IF(A2< B2, "Initiate Cut down", "Unacceptable")))

      where A2 is the Actual Expenditure and B2 is the Estimated Expenditure.

      All the best,
      Radu

      Reply

    • Hi Radu

      I am unable to see your reply. I think It occurred for brief moment then got deleted.
      If its not trouble, can you please share the solution once more.

      Regards

      Reply

      • Hi Neil,

        It should be visible now. There was a problem with the CDN which had cached the older version.

        I hope you can see it now.

  • Hello. I have 3 conditions. If a tree is less than 14.0″, the following cell should be “N/A”. If the tree is 14.0100″, the following cell should be “HERITAGE”. I can only make the cell work for two of the three desired statements. I am also getting a false statement.

    This is what I am using at the moment. Can you please assist?
    =IF(H8<14, "N/A", IF(H8=14100, “HERITAGE”)))

    Reply

    • Hi Matt,

      I see two potential issues with your formula:
      1) you do not use the higher than sign in the second IF statement which is only triggered if the height of the tree is equal to exactly 14100
      2) you use 14100 instead of 14.0100, as you mentioned in the first part of your comment

      I think you could just as easily write it using only one IF statement since I understand that any tree taller than 14.00 (starting with 14.0100 and up) is a HERITAGE. I would just use this formula:

      =IF(H8<=14, "N/A", "HERITAGE")

      This means that any value lower than or equal to 14.000000 the formula will return N/A. In all other cases (meaning values higher than 14.000000) the formula will return HERITAGE.

      Let me know if this helps.

      Reply

  • I would love some help to try and work this one out please.

    there are 3 of us travelling and we have each paid different things towards the accommodation. I have columns of what we owe each person, based on what they have paid for. As the table still needs updating, I thought it would be much easier to have a formula in there….well so I thought, but perhaps my formula is too over complicated, so it won’t work.

    So I have 6 columns
    A owes B, A owes C, B owes A, B owes C, C owes A, C owes B

    So I have got totals for each column.

    working with A owes B and B owes A…….is where I want the formula to come in!

    So essentially if A owes B < B owes A, then A owes B nothing,……but I need to compare those figures,

    So, say A owes B = $100 and B owes A = $500

    I want to be able to return what they owe….so as B owes A $500, but A Owes B $100, this offsets each other, so B will owe A $400.

    so if the formula was to be put into A owes B, it should return a $0, as the amount is smaller, but if I put the formula in B owes A, it should return $400.

    The issue is…..that if amount changes and say B pays for something more before the holiday, then it becomes A owes B $600, I would still like the formula to work.

    Reply

    • Hi Shazza,

      I hope I got your question right. I would add up all the cells from column A owes B and compare the result with the sum of B owes A. Then, write an IF formula and check if A owes B is higher than B owes A.

      Let’s say that in column A you have all the amounts for which A owes B and in column B all the amounts for B owes A. You could write a formula to calculate how much B owes A like this:

      =IF(SUM(A1:A10)

      Reply

  • I hope you can help. This is one of the formula I have used and variations using AND and OR statements.

    =IF(F13+G13>0, K13, -G13)

    I need to add another condition. if G is <0 then 0
    EXAMPLE is
    So lets say F is -86782 and G is 65394. the sum is a negative ie less than 0 so it will return the value in -g13 which is a -65394.
    If the value in G only is <0 then I need it to return a value of 0

    so lets say f is -17455 and g is -75689 the sum is a negative but because G is a negative I need it to return a value of 0.

    Thank you.

    Reply

    • Hi Harminder,

      To achieve this you first need to check if G13<0 and only run the next part of the formula if G13 is a positive number. The formula you need is:
      =IF(G13<0, 0, IF(F13+G13>0, K13, -G13))

      Let me know if that works.

      Cheers,
      Radu

      Reply

      • Thank you Radu, that has worked. Amazing I must have tried very similar versions of this but probably missed something vital.

        Thank you
        Harminder

      • I’m glad it worked out 🙂 With nested IF statements, you always need to be aware that if the first condition is TRUE, the others will never be checked. So, if you were to write the same formula but with F13+G13>0 as the first condition, then G13<0 would never be checked if the first one is TRUE.

  • Hi! Hope you can assist. I have 3 tiers:
    If a person hits this spend requirement (cumulative transactions):
    Total spend rebate
    20,000 1,000
    50,000 2,500
    100,000 5,000

    What will be my IF formula for these 3 conditions? My goal is once the person hits the spend requirement, the remaining transaction/s will be tagged as invalid. Thanks

    Reply

    • Hi Sheena,

      Your explanation is a bit confusing. Would you please elaborate on this?

      I understand that you have three spending tiers (20000, 50000, and 100000) and that each tier has a rebate. What I don’t understand is what you expect the formula to do.

      Do you have another table where you are comparing the spending with the tiers and applying a rebate?
      What do you want the formula to return if a person is currently at 70000 spendings? Is it 2500? How about when it’s below 20000?
      Do you expect the transactions to be tagged as invalid once the spending reaches 100000+?

      Reply

  • Hello sir, thank you for your tutorial of if with 3 Conditon, It is amazing and useful.

    i’m trying the formnula with 2 “if” “and: with multiple condition desiring to results, But the response from the formula is “#VALUE” Please help in its modification as to give the result as desired

    Cell B77 & D77 conatain segment and no of calendar days respectively.

    Formula is =IF(AND(B77=”Debt”, D77>365*3), “Index”, “No Index”), IF(AND(B77=”Equity”, D77>365*1), “FMV”, “No FMV”)

    Reply

    • Hi Dinesh,

      The problem with your formulas is that you are actually not nesting IF functions. You just wrote two IF statements linked by a comma which confuses Excel. I have split your formula into two rows so that you can see that they are separate and not linked:

      =IF(AND(B77=”Debt”, D77>365*3), “Index”, “No Index”),
      IF(AND(B77=”Equity”, D77>365*1), “FMV”, “No FMV”)

      You need to connect the multiple IF statements as I showed in the tutorial. Now the issue I see is that you have two sets of conditions and two different value_if_false possible outcomes: “No Index” and “No FMV”

      Usually, with nested IF statements you have one default value that captures all the cases when your formula returns FALSE. So, to achieve your goal, the formula gets a little more complex:

      =IF(B77=”Debt”, IF(D77>365*3, “Index”, “No Index”), IF(B77=”Equity”, IF(D77>365*1, “FMV”, “No FMV”), “”))

      I have added double quotes so that if B77 is empty it will not return FALSE. Instead, the cell will look blank.

      Let me know if this is what you were looking for.

      Reply

  • Hello sir, thank you for your instructions. You’ve been a great help to me.

    I’m trying to have a cell show one of three different numbers. I can get it to pick one of two, but I can’t wrap my head around having it choose one of 3.

    If I have three numbers: 40.0, 50.0, and 60.0
    If a number in cell A1 equals 40 or greater, but less than 50, I want it to put the data it gets from a certain cell.
    If the number in A1 is greater than 50 but less than 60, then I want it to put a number found in a different cell.
    And if the number in A1 is greater than 60, then I want it to put a number found in a different cell.

    For example, I have a mechanic that gets a bonus dependent on how many hours they ear for that week.
    The bonus may be different for each technician so I have a table for which tier they hit.
    If tech A gets more than 40 hours, he gets an extra $1/hr. If he gets over 50 hours, he gets an extra $2/hr, if he gets over 60, he gets an extra $3/hr.
    I hope I haven’t confused it by overexplaining.

    Reply

    • Hi Josh,

      The formula would look like this:
      =IF(AND(A1>=40, A1<50), B1, IF(AND(A1>=50, A1<60), C1, IF(A1>=60, D1)))

      Just replace cells B1, C1, D1 with the result you want to retrieve, and you should be good to go. Keep in mind that the formula will return FALSE if the value in cell A1 is lower than 40 since there is no condition to catch that particular case.

      All the best,
      Radu

      Reply

  • Hello!

    Can you please help me solve this.
    The function has to say “Wrong lenght” when the Ikood has more than 11 numbers. Also when Ikood starts with number 3 or 5 it has to say “Mees” and when it starts with 4 or 6 it has to say “Naine” otherwise when Ikood starts with any other number it has to say “The first symbol is wrong”.

    I tried this type of formula, but I does not seem to work

    =IF(LEN(isikukood)11; “Wrong lenght”; IF(OR(LEFT(isikukood)=”3″; LEFT(isikukood)=”5″); “Mees”; IF(OR(LEFT(isikukood)=”4″; LEFT(isikukood)=”6″); “Naine”; “Wrong first symbol”)))

    Thanks

    Reply

    • Hi,

      I think you only missed the greater than (>) symbol from the first condition that checks the length of the string. Here is the formula that worked for me:
      =IF(LEN(isikukood)>11; “Wrong lenght”; IF(OR(LEFT(isikukood)=”3″; LEFT(isikukood)=”5″); “Mees”; IF(OR(LEFT(isikukood)=”4″; LEFT(isikukood)=”6″); “Naine”; “Wrong first symbol”)))

      Let me know if it works as intended.

      All the best,
      Radu

      Reply

    • Hi Rachael,

      Please let me know if the formula is working for you.
      =IF(OR(E2=2058, E2=2044, E2=2033, E2=2070), N2 – VLOOKUP(E2, W4:X10, 2, 0)*K2)

      All the best,
      Radu

      Reply

  • Hi!
    I’m struggling a bit with one calculation – when should customer’s orders be packed based on ordering time.

    I have customer order time in cell C6 (YYYY-MM-DD hh:mm:ss).
    In cell D6 I have weekday (text).
    In cell E6 I have condition – should the order be sent out today (Yes/No). This is calculated based on what time customer placed the order – orders placed before 11:00AM should be packed the same day, latest 15:00; orders placed after 11:00 can be packed latest the next working day 15:00.

    In Cell F6 I have calculated LATEST packing time for specific order:
    =IF(E6=”Yes”; CONCATENATE(TEXT(C6; “yyyy-mm-dd”); ” 15:00:00″); IF(E6=”NO”; CONCATENATE(TEXT(C6+1; “yyyy-mm-dd”); ” 15:00:00″))).

    And now finally my question 🙂 – my formula does not count weekends. No one picks and packs on Saturday and Sunday. So I need to add conditions for correct packing time:
    If order is placed on Friday after 11AM, latest packing time is Monday 15:00.

    Could you please help me to figure this out?

    Reply

    • Hi Maret,

      If I understood your question correctly, I think this should solve your missing conditions:

      =IF(E6=”Yes”, CONCATENATE(TEXT(C6, “yyyy-mm-dd”), ” 15:00:00″), IF(E6=”NO”, IF(WEEKDAY(C6, 2)=5, CONCATENATE(TEXT(C6+3, “yyyy-mm-dd”), ” 15:00:00″), IF(WEEKDAY(C6, 2)=6, CONCATENATE(TEXT(C6+2, “yyyy-mm-dd”), ” 15:00:00″), IF(WEEKDAY(C6, 2)=7, CONCATENATE(TEXT(C6+1, “yyyy-mm-dd”), ” 15:00:00″))))))

      Let me know if it works as intended.

      Radu

      Reply

  • Hi,

    Can You please solve this

    There are two types of vehicles, small vehicles and Large vehicles, both two types of vehicles carry different weights. so there are different rates for both types. so my question is, how to write a if statement to consider both conditions, lets say

    if it is a small vehicle and it is between 1-2 MT tonnage group then the answer should be “1-2MT S”,
    if it is large vehicle and it is between 1-2 MT tonnage group then the answer should be “1-2MT L”

    Thanks

    Reply

    • Hi Jaze,

      Is 1-2 MT tonnage range defined by numbers? What I mean by that, is the tonnage between 1 and 2? If yes, then the formula would look something like this:
      =IF(AND(A1=”small vehicle”, B1>1, B1<2), "1-2MT S", IF(AND(A1="large vehicle", B1>1, B1<2), "1-2 MT L"))

      Cell A1 contains the type of vehicle. Change that and the name "small vehicle" as appropriate. Cell B1 is used for the tonnage, and the formula checks if the tonnage is between 1 and 2.

      Let me know if this is what you were looking for.

      Radu

      Reply

  • I tried to create an If/And/Or statement using rotating data points. For example, if one cell has a number and another cell has a month, I want a specific outcome to display in the formula. If a number is between 900 and 1,000 and the month is March or April one outcome should display. If either the number is out of the aforementioned range AND/OR the month listed is out of the range, the outcome should differ. However, I have multiple ranges to search.

    If number is 900-1000 during March or April – display outcome one, if not display outcome two, if number is 800-900 during January or February, display same outcome one as preceded, if not, display outcome two, if number is 700-900 during November or December, display outcome one, if not display outcome two.

    Reply

    • Hi William,

      It’s hard to tell how your formula should look like without actually knowing all the conditions involved. If you prefer, you can send me an email via the contact page and I’ll help you out. There may be a better option than using nested IF statements.

      Reply

  • Hi, I have a s/sheet with times in a column and am trying to identify more easily where the time is either before or after 08:30 and am currently using the current IF statement

    =IF(COUNTIF(E2, “<="&TIME(8,30,0)), "Before 08:30", "After 08:30")

    This works where the cell has a date there are instances where the data is incomplete and either the cell is blank or has #N/A. In those instance I want to return the text – No Booking in time. Can you show me how this can be achieved please.

    Reply

    • Hi Andrew,

      There is no need to use COUNTIF in this formula, as you are not counting values in a range of cells. If I got your requirement right, the following formula should solve your issues:

      =IF(OR(ISBLANK(E2), ISNA(E2)), “No Booking in time”, IF(E2<=TIME(8, 30, 0), "Before 08:30", "After 08:30")) Let me know if that works. All the best, Radu

      Reply
    • >