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. I am looking for a formula that does the following for an answer in cell J2:
    If cell B2=300 then =SUM(D2)*0.03
    If cell B2=400 then =SUM(D2)*0.04
    If cell B2=500 then =SUM(D2)*0.05

    In other words, cell J2 will calculate IF cell B2 is equal to 300, 400 or 500...then the result will calculate the amount in cell D2 and multiply it by 0.03,0.04 or 0.05.

    I thought this was simple enough...but can't quite get it right. Any assistance would be greatly appreciated!

    • Hello!
      You can use this formula:

      =IF(B2=300,D2*0.03,IF(B2=400,D2*0.04,IF(B2=500,D2*0.05,"")))

      or

      =IFS(B2=300,D2*0.03,B2=400,D2*0.04,B2=500,D2*0.05)

      You can learn more about multiple conditions and nested IF in Excel in this article.
      The formula SUM(D2) doesn't make sense.

  2. Please help me

    This is the formula i am using

    IF(AND(E6>0,F6>0,I6="PS"), "Yes", IF(AND(F6>0,I6="OP"), "Yes", "No"))

    Cell value are E6=1, F6=0, I6=PS, I6= OP

    In evaluating formula from Formulas>Evaluate Formula showing #N/A but the output is correct.

    IF(False,#N/A, IF(AND(F6>0,I6="OP"), "Yes", "No"))

    How to over come #N/A

    • Hi!
      I have not been able to replicate your problem. Perhaps a formula is written in cell F6. When checking the condition F6> 0 using Evaluate Formula, Excel tries to calculate it and gets an error. Evaluate Formula cannot evaluate the formula in another cell.

  3. Date USD GBP AUD
    01-04-21 72 105 55
    18-04-21 72.5 104.5 55.25
    02-05-21 71 102 53.8
    15-05-21 72.4 103 55
    01-06-21 73.25 105 56

    Date Amount Ex.Rate Total
    02-04-21 USD 10.25 PLEASE SUGGEST FORMULA TO GET APPlICABLE EXCANGE RATE AS PER THE DATE AND CURRENCY SYMBOL
    01-04-21 GBP 10.40
    01-04-21 AUD 25.50
    05-04-21 USD 220.10
    03-04-21 GBP 105.80
    04-04-21 AUD 205.25
    01-04-21 USD 150.50
    02-04-21 GBP 150.18
    18-05-21 USD 165.25
    10-05-21 AUD 190.75
    30-05-21 USD 135.25

  4. Help!
    • Shipping Cost which is the cost based on the Region and weight
    How do I get this using the two tables below?
    i tried If (and but it is very long and I'm getting confused any suggestions?

    A B C D E

    1 Region Weight Shipping Cost

    2 North America 1
    3 Asia 0.5
    4 Caribbean 5
    5 Caribbean 2
    6 Caribbean 7.9
    7 North America 20

    8 Shipping Origination 0.5 – 5 Kg 5.1 – 10 kg 10.1 – 15 kg Over 15 kg

    9 South America $5,000 $8,000 $12,000 $15,000
    10 Europe $18,000 $10,000 $15,000 $18,000
    11 Caribbean $2,000 $6,000 $13,000 $18,000
    12 Africa $20,000 $25,000 $35,000 $50,000
    13 Australia $25,000 $35,000 $47,000 $60,000
    14 Asia $20,000 $31,000 $45,000 $58,000
    15 North America $3,000 $6,500 $13,350 $18,770

  5. Region Weight QTY Cost
    North America 1 3 $1,230.00
    Asia 0.5 21 $3,330.00
    Caribbean 5 3 $3,340.00
    Caribbean 2 54 $3,350.00
    Caribbean 7.9 21 $2,330.00
    North America 20 32 $54,310.00

  6. HOW CAN WE CALL LOWER VALUE AGAINST SOME FILED LIKE THAT

    CONTAINER VALUE
    ABCS1234567 1
    ABCS1234567 2
    ABCS1234567 3
    ABCS1234567 4

    WE NEED LOWER VALUE AGAINST SAME CONTAINER NUMBER

    IN EXL

    THANKS

    • 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:

      =MIN(FILTER(B1:B10,$A$1:$A$10=A1))

  7. HOW CAN WE CALL LOWER VALUE AGAINST SOME FILED LIKE THAT

    CONTAINER VALUE
    ABCS1234567

  8. you can try this

    =IF(AND(L13>0,L134,L138,L1316,L1320,L1342,L1350,"25")))))))

  9. Hi there,

    Did I get a situation with 6 conditions, How to write this excel formula?

    OD Allowance
    O≤Ø4 0.8mm
    Ø4<O≤Ø8 1.5mm
    Ø8<O≤Ø16 2.0mm
    Ø16<O≤Ø20 2.5mm
    Ø20<O≤Ø42 3.5mm
    O≤Ø50 5mm

    The allowance must add to the Actual OD value。 In other words, Actual OD + Allowance, actual OD varies ranging 0 to 50 and above.
    Hope to hear from you.
    Thank you.

  10. I am looking for help with a formula.
    Column C is the Application Date

    Column D is the Application Expiration Date. Formula in that cell is: =IF(C132="","No Start Date",DATE(YEAR(C132)+3,MONTH(C132),DAY(C132)))

    Column E is "Days Left". That formula, and I don't know if it's correct or not is: =IF(D133="No Start Date","0",D133-TODAY())

    Column F is where I need help. Base Date off of a Today Date of 5/20/2021

    Column C Column D Column E Column F
    App Date Exp Date Days Left STATUS
    2/08/2011 11/10/2020 -191
    No Start Date 0
    3/11/2019 3/11/2022 295
    5/29/2018 5/29/2021 9

    What I want is a formula that would be in the STATUS Column that states IF Column E is 0 or less as in a negative number, I want the STATUS to read "EXPIRED. If the Days Left number is =1, I want it to read "RENEW NOW" AND if possible, if Column D Reads "No Start Date", I want the STATUS to read "No Start Date"
    Thanks for your help. I have not been able to get any formula to work.

  11. Hi,
    I am trying to use a toggle to say, if the cell = 1, answer is cell a, if the cell = 2, answer is cell b, if the cell = 3, answer is cell c etc. How would I solve this?

  12. I have been using two different formulas to get my data but I would like to combine them.
    Example: Cell (D) has a date (30 June 2021)
    Cell (E) might have a date or might not have one : (blank)
    In Cell (I) I want to subtract Cell (d) and cell (E). But if Cell (E) does not have a date use "DATE"
    Two formulas are:
    =IF(ISBLANK(D8),"",-DATE(2021,4,1)+D8) used if there is no date in cell (E)
    =IF(E8="","NA",$D8-E8) Used if there is a date in Cell (E)

    How would I combine these?

    Thank you,

    Delila

    • 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:

      =IF(E8="",IF(ISBLANK(D8),"",-DATE(2021,4,1)+D8),$D8-E8)

  13. Trying to create a conditioning format with a number that looks like this ($20.00). I need all numbers greater then that in a range of ($20.00) to ($500.00) to be highlight white letters and back fill, but it will not do it? I know it is something so simple I am hitting wrong?

  14. Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L less than greater than symbol "Completed"),'Sheet B'!$P:$P,0)

  15. not sure why the sign will not show between $L:$L and "Completed", but is in my formula on my sheet.

  16. Need help with this:
    Customer Name - Sheet A Column C = Customer ABC (a list of 166 Customers)
    Customer Name - Sheet B Column A = Customer ABC (a list of 166 Customers)
    Customer Folder - Sheet B Column D = Folder #6 (folders range from 1-6)
    Customer Completed - Sheet B Column L = Blank, Not Started, WIP or Completed
    Days to Complete - Sheet B Column P = from today to ETA (this can also be a negative if it is pass due)

    Want to know in a particular folder how many days to complete if not completed.

    Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L"Completed"),'Sheet B'!$P:$P,0)

    Results have = 0 regardless of the true results.

    • Correction:

      Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L"Completed"),'Sheet B'!$P:$P,0)

      • Hello!
        Unfortunately, without seeing your data it is difficult to give you any advice. I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

        We'll look into your task and try to help.

  17. Requirement Shortfall
    stock month 1 month 2 month 3 month 1 month 2 month 3
    item A 300 400 500 600 -100 -500 -600
    item B 100 50 400 600 0 -350 -600
    item C 200 50 100 100 0 0 -50

    how to calculate shortfall for three months

  18. Thanks for this, But I can't seem to manage my formula.

    I need to know the following:

    If value (D1) is 5000 and 10000 and 20000 then 4

    =IF(D15000,2,IF(D1=10000, 3,IF(D1=20000,4)))))

    Afterwards I need to know how many times there was 1,2,3 and 4 in the formula row of course.

    What am I doing wrong?
    Thanks in advance!

  19. Hi Alexander,

    thanks for your reply.

    can I share my Excel file with you?

  20. Hi,

    thanks for sharing such amazing excel stuff.

    I will be grateful if you can help me with this formula:

    =IF(AND($F$3=1,M6>L6),J6*M6,J6*L6,IF(AND($F$3=2,N6>M6),J6*N6,J6*M6,IF(AND($F$3=3,O6>N6),J6*O6,J6*N6,IF(AND($F$3=4,P6>O6),J6*P6,J6*O6,IF(AND($F$3=5,Q6>P6),J6*Q6,J6*P6)))))

    I am trying to calculate value of work done for each month , (F3 is custome list of months from 1 to 12)
    M6 is Jan 21 and L6 is Dec 20 and J6 contract value. if the subsquent month is zero or less than previous month % than, the value should arrive from previous month % multiplied by contract Value.

    Kind regards,
    Hussain

    • Hi,
      The information you provided is not enough to understand your case and give you any advice, sorry. But I see J6*M6. Are you multiplying the date by a number? Also, your IF functions are not nested within each other, but simply written one after the other.
      Here is the article that may be helpful to you: Nested IF in Excel – formula with multiple conditions.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

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