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. Is there a formula where it will tally the number of times a specific phrase of words or a specific number is used in a range of cells which span 4 columns?

  2. wanted to see if you can help with reading negative numbers, probably is much easier than expected.

    So far I have tried changing the format of the numbers but didn't work, see below example

    my formula

    =IF(AND(G3>=$AF$3,G3=$AF$4,G3=$AF$5,G3=$AF$6,$AE$6,""))))

    Table array with a mixture of positive and negative numbers

    Day from Day to
    -28 -21
    -21 -14
    -13 -7
    0 14
    15 28

    • Hi! Unfortunately, your formula is written incorrectly, and I can't guess what it means and what you were trying to achieve. Please explain your problem in detail.

      • The table array didn't help, sorry about that

        I have a list of numbers (both positive and negative) and based on the negative numbers I wanted to output
        i.e. if between -5 and -10 "1 week"
        -11 and -20 "2 week"
        -21 and -28 "3week" etc

        This works for positive numbers, its negative numbers which is proving difficult. I was hoping the IF AND statement would work for this scenario but doesn't

        • Hi! Sorry, I do not fully understand the task. Your clarification did not make the question any clearer. How do you get weeks from numbers. Your question is not entirely clear, please specify.

  3. Hello,
    Would appreciate your help on this! So my formula isn't ignoring the negative numbers like I expected. I also tried fixing my problem with if and statements but the way in which I put it doesn't help either. * note I don't have MINIFS so i used nested if statement.

    =MIN(IF('Train '!B:B=Sheet1!A1,IF('Train '!E:E>=Sheet1!K2,'Train '!E:E0,'Train '!F:F))))

  4. I am going to explain what I want the formula to do since it is not printing the formula. I apologize for the multiple emails! I want C5 to be evaluated and, if the number is less than 500, I want it to assign the cost of $500, if that number is greater than 500 but less than 1,000, I want it to assign the cost of $1,000, if that number is greater than 1,000 but less than 1,500, I want it to assign the cost of $1,500...

    I have numbers in the column that I want to evaluate and assign a cost for that range from under 500 to over 13,500. Am I going about this the best way? Thanks in advance for your help!

  5. I want to use an IF statement with multiple conditions but can't seem to get the formula just right. Here is the formula I am trying to use that comes back with the error "The formula is missing an opening or closing parenthesis. =IF(AND(C550010001500<2000, "2000")

    I want it to evaluate the number in C5 and, based on multiple conditions, assign a cost associated with that range. Help! I'm desperate!

    • Hi! Please re-check the article above since it covers your task. All conditions in the AND operator must be enclosed in parentheses. Your formula is written incorrectly, but there should be a parenthesis after the number 2000)

  6. Hello,

    I am trying to write a formula that does the following:

    I want the function to look at a cell (G9) and determine if it's greater than 0, then compare a column of numbers (C4:C7) to see if there is a match to the largest number in the column by comparing it to another cell (C4) and return a 1 if both conditions are met. This is what I have so far:

    =IF(AND(G9>0,(MAX(C4:C7)=C4)), 1, 0)
    IF Grand Total for Delta is greater than 0 and Cadets in current row is equal to the biggest number in Reg column, then return a 1, otherwise 0

    I also need to keep a running total of Grand Total for Delta (G9). G9 is my remainder and I want to assign the remainder one at a time to the largest to smallest numbers in my array. So my formula must also take into account when my remainder is 0 (But also not changing G9).

    This is what I want the data to look like:

    A B C D E F G
    3. Region Cadets Reg Det Aggregate Carryover Delta
    4. NE 411 41 23 15 3 0
    5. NW 354 35 19 10 6 0
    6. SE 493 49 34 8 6 1
    7. SW 449 44 29 10 5 1

    9. Grand Total 1,707 169 105 43 20 2

    I had a Grand Total Delta of 2, therefore SE and SW Regions receive 1 each

    • Hi! Based on your description, it is hard to completely understand your task. I will try to assume that your formula in G7 refers to cell G9. The value in G9 is given as the sum of G4:G7. You have a circular reference.

  7. I am trying to get that "Yes" and "No" TAT. I don't get the formula well.
    =IF(AND(E12>23,E12>10),"No",IF(AND(E12<23,E12<10),"Yes","No"))
    I also used this formula but it didn't push through with "Yes" No".
    =IF(AND(O12,"Building to Building",E12""),"Yes","")=IF(AND(O12,"Within the Floor",E12""),"Yes","")
    Type of Request SLA
    Within the Floor 10
    Floor to Floor 10
    Building to Building 23

    • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Note how to correctly use IF AND is described in the article above.

  8. Pleas e help,
    How to create a formula for the following information

    Column A Column B Column C Column D Column E Column F Column G
    Milestone date Amount Paid on Milestone date Amount Paid on Total Paid

    Problem: I want to add the total amounts paid in column G (total paid) with the amounts of columns B and E ONLY if there is a date in columns C and/or F.

    Really appreciate your help

  9. I pull service numbers every quarter from our database and put into a spreadsheet because I'll be reporting them by county to 25 entities (some get the results of 1 county, some up to 7 counties) and each county has its own tab in the workbook. The spreadsheet compares the current year to 2 previous years in 15+ categories (rows). I have 3 YTD columns (one for this year and one for each of the last two) as well as 12 columns representing each of the 4 quarters for each year. It never fails that when the next quarter comes around I'll forget to update one of the previous YTD calculations and my numbers are all skewed.
    So for example, in Q1 a particular cell should read "=c3", and then Q2 the formula would be "=c3+f3", with Q3 adding in i3 and Q4 adding in L3.
    Is there a way I can get it to choose which calculation is done if I add a quarter reference somewhere. If the reference cell says 2 it'll return the result of "=c3+f3", if it says 4, it'll return the result of "c3+f3+i3+l3"?

  10. Ugh, I'm stuck here...trying to figure out how to pull data within a range and it's not working.

    Basically I can pull the info I need from the C column with this formula on anything that matches F as 7 or greater:

    =IF(F3:F5>6.9,C3:C5,"")

    I can also do the same for anything 3 and under by using: =IF(F3:F5<3.1,C3:C5,"")

    However, I want to pull the same data for the range 4-6 and I can't figure out how...I've tried a couple of things including:
    =IF(AND(F3:F5<3.1,F3:F5 or 3.9 and <6.1 I can't figure out the formula...what am I missing?

  11. I need someone to tell me why I am not getting proper calculations:

    Below Time sheet calculations:

    Monday through Friday will have a "Y" in the E column. Y represent 7.5 hours

    However, if there is a value in G column (represent time off/leave time), needs to be deducted from 7.5 hour rate

    E Column contains either Y or N (Y = Worked 7.5 hrs, N = not work 0 hours
    G Column contains numbers (leave time). If N is used, the value is added to total hours in the total cell. However, if a person enters a Y and worked 1/2 day and took 1/2 off, as an example: 4.5 hours worked, and 3 hours vacation time. The day's total is 7.5. That is why in the "false" box the line (7.5 - G10) is added. But below formulas is not adding up or reducing hours when value Y changed to N and vice versa.

    =SUM(IF(AND(E10="Y",G10=" "),7.5,(7.5-G10)))+SUM(IF(AND(E11="Y",G11=" "),7.5,(7.5-G11)))+SUM(IF(AND(E12="Y",G12=" "),7.5,(7.5-G12)))+SUM(IF(AND(E13="Y",G13=" "),7.5,(7.5-G13)))+SUM(IF(AND(E14="Y",G14=" "),7.5,(7.5-G14)))+SUM(IF(AND(E15="Y",G15=" "),7.5,(7.5-G15)))+SUM(IF(AND(E16="Y",G16=" "),7.5,(7.5-G16)))+SUM(IF(AND(E17="Y",G17=" "),7.5,(7.5-G17)))

    When I put this in the cell, I get weird number 52.20 of something, not 37.5 if there was G column has no data. The (7.5-G10) is simply want

    I am sure there may be a better way to calculate. I am still just going about it the long way...at this time.

    I appreciate if you have a better solution...email me if you can.

    Thank you everyone for participating on this discussion.

    Gil
    email: gilinnc @ gmail . com

  12. Hi Sir,

    I need a single formula to validate multiple OR & AND to show true or false value.

    Case1: If A2=Apple is harvested in large scale, B2=Fruit, C2=1
    here if A2 has a specific text "Apple" and B2=Fruit and C2=1, the value should be "True"
    OR
    Case2: if A2=Mango is harvested in large scale, B2=Fruit, C2=2
    here if B2=Fruit & C2=2, the value should be True

    So either(Case1 or Case2) meets the above cateria the value should be true.

  13. RATING Percentage
    5 95-100%
    4 75-94%
    3 45-74%
    2 31-44%
    1 0-30%

    I want to insert if multiple conditions formula to rate 1-5 based on the percentage for above performance index. please Guide in excel how to apply if formula

  14. THANKS A LOT

  15. Can someone help me to find out the correct date using the following conditions.
    ​Earlier than July 1, 1949 - Output Age 70.6
    On or after July 1, 1949, but before January 1, 1951 - Output Age 72
    On or after January 1, 1951, but before January 1, 1960 - Output Age 73
    On or After January 1, 1960 Output Age 75

    Output Should be in Date Format.

    Thanks in Advance

  16. HI, i have a real time data in dates and time (1/1/2022 1:00am) for a year but i want to reduce the data for January by 30% , for February by 60% and so on. please help with this .thanks

  17. I have a spreadsheet where the Date in column N is dependent on data in columns H and J.
    Results for each outcome are held in columns M2, V2 and U2.
    i.e If the length of service in column J is less than 6 years, the date in column N would point to the formula in column M. If the length of service in column J is 6 years or more AND start age in column H is less than 18 then the date in column N would point to the formula in column V but if the length of service in column J is 6 years or more AND start age in column H is 18 or more then the date in column N would point to the formula in column U. How would I combine all this to bring back the desired results?

  18. I am trying to convert the following information into a formula for BMI Status on a spread sheet, but I have been unsuccessful all I get is a value error and can't figure out why. What is wrong with the formula?

    Underweight 40

    Here is my formula:

    =IF(AND(G39=18.5, G39=25, G39=30, G39=35, G39=40),"Obese Class 3","")

  19. How to formulate? IF row 2 and 3 has same EEID, then it should sum up the amount of EEID in rows 2 and 3. Then the result in ROw 3 since same EEID in row 2 should be 0. If not same EE ID like Row 1 and 2 result should be same amount in row 1
    Thanks

    EEID Code Amount Result
    1 1856996 VNGW12 3,060,000.00 3,060,000.00
    2 1716885 VNGW18 1,179,150.00 2,054,150.00
    3 1716885 VNGW18 875,000.00 - 0.00
    4 1716887 VNGW18 875,000.00 875,000.00

  20. I have problem in computing results with excel.
    I want to combine two sheets together and make it print REMARK that contains PASS OR the subjects failed

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