IF AND formula in Excel

The tutorial shows how to use IF together with the AND function in Excel to check multiple conditions in one formula.

Some things in the world are finite. Others are infinite, and the IF function seems to be one of such things. On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time.

IF AND statement in Excel

In order to build the IF AND statement, you obviously need to combine the IF and AND functions in one formula. Here's how:

IF(AND(condition1, condition2,…), value_if_true, value_if_false)

Translated into plain English, the formula reads as follows: IF condition 1 is true AND condition 2 is true, do one thing, otherwise do something else.

As an example, let's make a formula that checks if B2 is "delivered" and C2 is not empty, and depending on the results, does one of the following:

  • If both conditions are TRUE, mark the order as "Closed".
  • If either condition is FALSE or both are FALSE, then return an empty string ("").

=IF(AND(B2="delivered", C2<>""), "Closed", "")

The screenshot below shows the IF AND function in Excel:
IF AND statement in Excel

If you'd like to return some value in case the logical test evaluates to FALSE, supply that value in the value_if_false argument. For example:

=IF(AND(B2="delivered", C2<>""), "Closed", "Open")

The modified formula outputs "Closed" if column B is "delivered" and C has any date in it (non-blank). In all other cases, it returns "Open":
IF AND formula in Excel

Note. When using an IF AND formula in Excel to evaluate text conditions, please keep in mind that lowercase and uppercase are treated as the same character. If you are looking for a case-sensitive IF AND formula, wrap one or more arguments of AND into the EXACT function as it is done in the linked example.

Now that you know the syntax of the Excel IF AND statement, let me show you what kind of tasks it can solve.

Excel IF: greater than AND less than

In the previous example, we were testing two conditions in two different cells. But sometimes you may need to run two or more tests on the same cell. A typical example is checking if a cell value is between two numbers. The Excel IF AND function can easily do that too!

Let's say you have some sales numbers in column B and you are requested to flag the amounts greater than $50 but less than $100. To have it done, insert this formula in C2 and then copy it down the column:

=IF(AND(B2>50, B2<100), "x", "")
IF formula to check the 'greater than AND less than' condition

If you need to include the boundary values (50 and 100), use the less than or equal to operator (<=) and greater than or equal to (>=) operator:

=IF(AND(B2>=50, B2<=100), "x", "")
Find values between two numbers, including the boundary values.

To process some other boundary values without changing the formula, enter the minimum and maximum numbers in two separate cells and refer to those cells in your formula. For the formula to work correctly in all the rows, be sure to use absolute references for the boundary cells ($F$1 and $F$2 in our case):

=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")
IF AND formula to flag values between the specified numbers

By using a similar formula, you can check if a date falls within a specified range.

For example, let's flag dates between 10-Sep-2018 and 30-Sep-2018, inclusive. A small hurdle is that dates cannot be supplied to the logical tests directly. For Excel to understand the dates, they should be enclosed in the DATEVALUE function, like this:

=IF(AND(B2>=DATEVALUE("9/10/2018"), B2<=DATEVALUE("9/30/2018")), "x", "")

Or simply input the From and To dates in two cells ($F$1 and $F$2 in this example) and "pull" them from those cells by using the already familiar IF AND formula:

=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")
IF AND formula to find dates that fall within a specified range

For more information, please see Excel IF statement between two numbers or dates.

IF this AND that, then calculate something

Apart from returning predefined values, the Excel IF AND function can also perform different calculations depending on whether the specified conditions are TRUE or FALSE.

To demonstrate the approach, we will be calculating a bonus of 5% for "Closed" sales with the amount greater than or equal to $100.

Assuming the amount is in column B and the order status in column C, the formula goes as follows:

=IF(AND(B2>=100, C2="closed"), B2*10%, 0)
If the specified conditions are TRUE, then calculate something

The above formula assigns zero to the rest of the orders (value_if_false = 0). If you are willing to give a small stimulating bonus, say 3%, to orders that do not meet the conditions, include the corresponding equation in the value_if_false argument:

=IF(AND(B2>=100, C2="closed"), B2*10%, B2*3%)
IF AND formula to perform different calculations depending on whether the conditions are TRUE or FALSE

Multiple IF AND statements in Excel

As you may have noticed, we have evaluated only two criteria in all the above examples. But there is nothing that would prevent you from including three and more tests in your IF AND formulas as long as they comply with these general limitations of Excel:

  • In Excel 2007 and higher, up to 255 arguments can be used in a formula, with a total formula length not exceeding 8,192 characters.
  • In Excel 2003 and lower, no more than 30 arguments are allowed, with a total length not exceeding 1,024 characters.

As an example of multiple AND conditions, please consider these ones:

  • Amount (B2) should be greater than or equal to $100
  • Order status (C2) is "Closed"
  • Delivery date (D2) is within the current month

Now, we need an IF AND statement to identify the orders for which all 3 conditions are TRUE. And here it is:

=IF(AND(B2>=100, C2="Closed", MONTH(D2)=MONTH(TODAY())), "x", "")

Given that the 'current month' at the moment of writing was October, the formula delivers the below results:
Multiple IF AND statements in Excel

Nested IF AND statements

When working with large worksheets, chances are that you may be required to check a few sets of different AND criteria at a time. For this, you take a classic Excel nested IF formula and extend its logical tests with AND statements, like this:

IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3, output4)))

To get the general idea, please look at the following example.

Supposing you want to rate your service based on the shipment cost and estimated time of delivery (ETD):

  • Excellent: shipment cost under $20 and ETD under 3 days
  • Poor: shipment cost over $30 and ETD over 5 days
  • Average: anything in between

To get it done, you write two individual IF AND statements:

IF(AND(B2<20, C2<3), "Excellent", …)

IF(AND(B2>30, C2>5), "Poor", …)

…and nest one into the other:

=IF(AND(B2>30, C2>5), "Poor", IF(AND(B2<20, C2<3), "Excellent", "Average"))

The result will look similar to this:
Nested IF AND statements

More formula examples can be found in Excel nested IF AND statements.

Case-sensitive IF AND function in Excel

As mentioned in the beginning of this tutorial, Excel IF AND formulas do not distinguish between uppercase and lowercase characters because the AND function is case-insensitive by nature.

If you are working with case-sensitive data and want to evaluate AND conditions taking into account the text case, do each individual logical test inside the EXACT function and nest those functions into your AND statement:

IF(AND(EXACT(cell,"condition1"), EXACT(cell,"condition2")), value_if_true, value_if_false)

For this example, we are going to flag orders of a specific customer (e.g. the company named Cyberspace) with an amount exceeding a certain number, say $100.

As you can see in the below screenshot, some company names in column B look the same excerpt the characters case, and nevertheless they are different companies, so we have to check the names exactly. The amounts in column C are numbers, and we run a regular "greater than" test for them:

=IF(AND(EXACT(B2, "Cyberspace"), C2>100), "x", "")

To make the formula more flexible, you can input the target customer name and amount in two separate cells and refer to those cells. Just remember to lock the cell references with $ sign ($G$1 and $G$2 in our case) so they won't change when you copy the formula to other rows:

=IF(AND(EXACT(B2, $G$1), C2>$G$2), "x", "")

Now, you can type any name and amount in the referenced cells, and the formula will flag the corresponding orders in your table:
Case-sensitive IF AND function in Excel

IF OR AND formula in Excel

In Excel IF formulas, you are not limited to using only one logical function. To check various combinations of multiple conditions, you are free to combine the IF, AND, OR and other functions to run the required logical tests. Here is an example of IF AND OR formula that tests a couple of OR conditions within AND. And now, I will show you how you can do two or more AND tests within the OR function.

Supposing, you wish to mark the orders of two customers with an amount greater than a certain number, say $100.

In the Excel language, our conditions are expressed in this way:

OR(AND(Customer1, Amount>100), AND(Customer2, Amount>100)

Assuming the customer names are in column B, amounts in column C, the 2 target names are in G1 and G2, and the target amount is in G3, you use this formula to mark the corresponding orders with "x":

=IF(OR(AND(B2=$G$1, C2>$G$3), AND(B2=$G$2, C2>$G$3)), "x", "")

The same results can be achieved with a more compact syntax:

=IF(AND(OR(B2=$G$1,B2= $G$2), C2>$G$3), "x", "")

IF AND OR formula in Excel

Not sure you totally understand the formula's logic? More information can be found in Excel IF with multiple AND/OR conditions.

That's how you use the IF and AND functions together in Excel. Thank you for reading and see you next week!

Practice workbook

IF AND Excel – formula examples (.xlsx file)

Table of contents

482 comments

  1. Hi Alexander, I am having difficulty in getting the right answer when I combine these two formulas:

    1st Data - Column/Row J12: Sports Information and Resource Centre (SIRC) and Column/Row K12: Museum(Sports Museum) >> Level 1 >> D-Blackbox Theatre #01-04, 05-CC01-211
    Formula: = IF(J12 = "Sports Information and Resource Centre (SIRC)", MID(K12,SEARCH("(",K12)+1,SEARCH(")",K12)-SEARCH("(",K12)-1), J12)
    Return: Sports Museum //correct

    2nd Data - Column/Row J54: Precinct (PCT) and Column/Row K54: WSC >> Stadium Riverside Walk (Board Walk)
    Formula: =IF(J54="Precinct (PCT)",TRIM(RIGHT(SUBSTITUTE(K54,">",REPT(" ",LEN(K54))),LEN(K54))))
    Return: Stadium Riverside Walk (Board Walk) //correct

    Combined Formula that I used:
    =IFS(J43="Sports Information and Resource Centre (SIRC)",MID(K43,SEARCH("(",K43)+1,SEARCH(")",K43)-SEARCH("(",K43)-1),IFS(J43="Precinct (PCT)",TRIM(RIGHT(SUBSTITUTE(K35,">",REPT(" ",LEN(K35))),LEN(K35)))),J43)

    Objective: To get the same correct results as above combining the two formulas I have created.

    Note: I have tried, IF(AND and IF(OR but the answer still incorrect.

    Hope you can assist me. Thank you!

    Regards,
    Aim

    • Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(J54="Precinct (PCT)",TRIM(RIGHT(SUBSTITUTE(K54,">",REPT(" ",LEN(K54))),LEN(K54))),IF(J12 = "Sports Information and Resource Centre (SIRC)", MID(K12,SEARCH("(",K12)+1,SEARCH(")",K12)-SEARCH("(",K12)-1), J12))

      For more information, please look at this article: Excel Nested IF statements - examples, best practices and alternatives.

      • Thanks a lot! Very helpful!

  2. I am trying to write a formula to compare two columns of text data that will include names of companies. In some instances, the names of the companies are not written in the exact same manner, however the company is one in the same (ex: The ABC Company and ABC Company). Is there a formula for this?

  3. Hi. I'm trying to write formula with statement as follow :

    cell P6 : Completion Date - Received Date
    cell O6 : Target Date - Received Date

    Formula as folliw
    =IF(P60), "DELAY", "OVER"))

    It's shows OK & OVER. But no showing DELAY for case of no input in the cell J6.

  4. I have to compare columns of values to see if C=D within $2.00. Basically we are accomodating the system rounding the calculation differently than the other company's system. So if Column C says $50 and column D says $48. I will want it to return true. Vice versa - if column C says $50 and Column D says $51.78 - I still want it to return true. And of course if column C = $50 and column D = $50, I want it to return True.

    I have used an if formula for this before, I believe, but of course I cannot find it in my past spreadsheets now :(

    Can you help?

  5. Hi, I have been using the nested formula for quite some time as below, it worked well, but however when I try to include one more extra condition the output I receive is 0. Kindly advise

    Old Working :

    =IF(AND(E7="Libya",H7="RF"),"Y",
    IF(AND(E7="Senegal",H7="RF"),"Y",
    IF(AND(E7="Tunisia",H7="RF"),"Y",
    IF(AND(E7="Yemen",H7="RF"),"Y",
    IF(E7="Reunion","y",
    IF(E7="Rwanda","y"," "))))))

    New : Not working

    =IF(AND(E6="Libya",G6="40",H6="RF"),"Y",
    IF(AND(E6="Senegal",G6="40",H6="RF"),"Y",
    IF(AND(E6="Tunisia",G6="40",H6="RF"),"Y",
    IF(AND(E6="Yemen",G6="40",H6="RF"),"Y",
    IF(E6="Reunion","y",
    IF(E6="Rwanda","y"," "))))))

    • Hi!
      I can't check your formula as I don't have your data. I can guess you are trying to compare the number 40 with the text string "40". Do not use double quotes for numbers.

  6. I am working on a spreadsheet to help organize files. What I would like to do is have a cell that calculates the date a file can be destroyed based on the type of case and date the case was closed. The case types in Column D are being pulled from a separate sheet, same workbook, so I can use the drop down feature to put them in my spreadsheet. The date the file is closed is in Column E and I would like Column F to auto-populate with the destruction date based on the date in Column E and the case type in Column D. I have been trying to accomplish this with conditional formatting since I will have several case types to add in.

    Example, if file is for attendance records that we are required to keep for 3 years:

    IF($D3="ATTENDANCE", DATE(YEAR(E3)+3, MONTH(E3), DATE(E3)), 0)

    So far none of the variations of this formula that I have tried have worked.
    Thank you!

  7. Hi there,
    I have been struggling to find a formula that will work in this situation. I need to know how many weeks between Approval Date and Start Date and then once I have that, IF that number of weeks is Greater Than 6 Weeks and IF that number of weeks is Greater Than 2 Weeks. Then I will show on a chart how many Approvals we have gotten on each project that were 6 Weeks or more in advance and how many were at least 2 weeks or more in advance. But my formulas keep coming up with errors.

    Approval Date Start Date Weeks Btw Approval to Start >6 Weeks >2 Weeks
    1/17/2023 3/15/2023 8.14 Yes6 Yes2
    1/30/2023 1/25/2023 #NUM! #NUM! No2
    11/16/2022 3/13/2023 16.71 Yes6 Yes2
    Under Review 1/31/2023 No6 No2

    • Hi!
      I don't know what formula you are using, but on the second row, the Start Date is less than the Approval Date. In the other rows, it is the opposite.

  8. I have been trying to see if I can create a formula that will help me with my clients. I am a nutrition coach, and in one of my cells I have a formula that calculates the BMI (Body Mass Index) of the person. BMI falls within six categories:

    18.5 = Underweight
    18.6 to 24.9 = Healthy Range
    25 to 29.9 = Pre-Obesity
    30 to 34 = Obesity Class 1
    35 to 39.9 = Obesity Class 2
    40 + = Obesity Class 3

    So in the cell next to it, I want it to display the class type.

    So let's say that cell B13 has the number, I want C13 to display what class they fall under.

    How would I go by doing that? I have tried multiple "IF AND" formulas and I just can't seem to get it right.

  9. Hello,

    Can you help me make a formula, I had it before but it got lost and now struggling to remake it.

    So I have a row for example A1 - B1 - C1

    if A1 is filled with for example £100 I need to minus 2% into with the sum into E1

    if A1 is empty but B1 is filled then I need it to minus 3% with the sum into E1

    then the same for C1 if A & B are empty but C is filled.

    Thanks!!

  10. Hi!

    I'm still unbale to identify the error on the following formula. Excell keeps replying back "There is a problem with the formula".

    any guidance will be appreciated.

    =IFERROR(IF(OR(X2>=5000),"DNB",IF(OR(W2-AN2=-500),"DNB",IF(OR(((W2-AN2))/AN2)>=0.1,"DNB", IF(OR(AND(T2="1'-Customer Shortage",S2="N",AN2>=5000,"DNB")))),"N/A").

    the formula works thill this statment:

    =IFERROR(IF(OR(X24>=5000),"DNB",IF(OR(W24-AN24=-500),"DNB",IF(OR(((W24-AN24))/AN24)>=0.1,"DNB"))),"N/A")

    however this portions, breaks it.

    IF(OR(AND(T2="1'-Customer Shortage",S2="N",AN2>=5000,"DNB")))),"N/A")

    Thanks!

    JV

    • Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IFERROR(IF(OR(X2>=5000),"DNB",IF(OR(W2-AN2=-500),"DNB",IF(OR((W2-AN2)/AN2>=0.1),"DNB", IF(OR(AND(T2="1'-Customer Shortage",S2="N",AN2>=5000)),"DNB")))),"N/A")

      OR operators are not needed in this formula.

  11. I am hoping to get the IF(AND) FORMULA FOR 30+10=36. BUT 30 + 5=35 ALL AS THE SAME FORMULA

  12. Hello Everyone,

    It’s my first time here, so I would like to thank everyone in advance for bearing with me and helping me out.

    Here’s what I’m trying to do … in as simple terms as I can explain … the problem is much complex …

    EXAMPLE:
    Client has to pay $30,000 (or whatever)
    He has 10 months to pay (or whatever)
    His monthly payment amount is consistent
    How do I calculate his last payment using a formula?

    Since I can’t post a spread sheet – here’s how I have it setup

    --- A3 TO B14 ---
    TOTAL PAYMENT
    BASE PAYMENT
    PAYMENT 1
    PAYMENT 2
    PAYMENT 3
    PAYMENT 4
    PAYMENT 5
    PAYMENT 6
    PAYMENT 7
    PAYMENT 8
    PAYMENT 9
    PAYMENT 10

    --- B3 TO B7 ---
    $30,000.00
    $9,000.00
    =B4
    =B4
    =IF(SUM($B$5:B6)<$B$3,$B$4,($B$3-(SUM($B$5:B6))))

    --- B7 TO B14 --- DRAG AND FILL FORUMLA ABOVE

    I’m usually pretty good at performing my own research for excel problems, but I’ve really been stuck on this one for a while – I’ve tried many different formulas, possibilities … but I can’t seem to figure it out ….

    In advance, please be nice … I’m no expert at excel.

  13. Hi,

    My excel query,
    Not sure I'm in right excel forum but anyway...

    Column 1 has 4 possible outcomes for a predicted blood group for a fetus, each one selected from a drop down box :
    Pos, neg, inconclusive, rejected

    Column 2 has actual blood group results when baby is born : pos,neg, unknown.
    How do i find out total number of predicted pos outcomes from A which have an unknown outcome in B.

  14. Hello sir
    I want to assign zero to above 0.5 and below 0.2
    I am using the formula
    =IF(AND(AE2>=0.85,AE2<=0.02)"0","AE2")
    please help me to write the correct one
    thanks and regards

    • Hi!
      All the necessary information is in the article above. Change the numbers in the formula and don't use quotation marks. Read the manual carefully.

  15. I want to give more than 0.85 and less than 0.02 to the zero else same value is it right ?????

    =IF(AND(AE2>=0.85,AE2<=0.02)"0","AE2"

  16. I need help with a formula, an IF formula i think. What i want to do is have a Cost cell change every time cells above it are changed. The cells above are on a dropdown. The drop down options are listed way below in the sheet so I can link those. I got it to work if its one option, but when I add multiple it doesnt work. I dont want the result to be Pass, or fail or a word, it needs to be a formula, for example, if cell A is changed to 2500 on the dropdown, the changing cell is decreased by 4% (.96). I am not sure if this makes sense but any help would be appreciated.

  17. Hi, Really new to excel and trying to get this formula to work. Not sure if I'm even on the right track.

    =IF(and(B10="true",A25="true",B25*52/12,"",if(and(b11="true",a25="true",B25*26/12,""))))

    I've been searching this site for help, but not sure I'm even in the right area.

    Thanks so much

  18. Hi,

    Here is my formula which actually works:
    =IF($H8="","",IF($H8=TODAY(),"Due Today",IF($H8TODAY()+15,"On Track",IF($H8>TODAY(),"Anticipated Past Due","")))))

    However, I need to add another condition which even if one of the above condition has been met but another cell (let us say L8) is not empty then the result should be Closed.

    How can I add that in the formula?

  19. Good morning,

    I'm trying to write a formula that completes the VLOOKUP calculation and not returning TRUE value:

    =IF($A$7="F30/28z Louvre",AND(C7>=1),D7-VLOOKUP(A7,Louvre_table,4)*2+20.1)

    Cell A7 has a drop down list of 4 different items, as seen above one is "F30/28z Louvre", if this is selected do the calculation:
    D7-VLOOKUP(A7,Louvre_table,4)*2+20.1) for the 3 other items I need to do this calculation: D7-VLOOKUP(A7,Louvre_table,4)*2+5.1)

    Hopefully you will be able to help, Thank you.

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF($A$7="F30/28z Louvre",D7-VLOOKUP(A7,Louvre_table,4)*2+20.1, D7-VLOOKUP(A7,Louvre_table,4)*2+5.1)

      I can't check the formula that contains unique references to your workbook worksheets.

      • Alexander,

        Thank you so much, I was trying everything and getting frustrated by adding to many variables & boom you solved it, Thank you.

  20. I am trying to write a condition that has 7 criterias. if all criteria are YES or a combination of YES or N/A then it will return "Completed" if blank or NO it will return "Not Completed"

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)