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

490 comments

  1. Hello- what formula would I need to write if I want to find all the workers that have the same primary and secondary skills? If I need all workers for example that have network skills, I would see that James, Bill, Bob and David have network skills. I have a list of about 200 employees. Can you assist?

    Employee Primary Skill Secondary Skill
    James Network
    Bill Firewall Network
    Bob Automation Network
    David Network Firewall
    Robert Data UC

  2. I need help with the below: I am looking for a formula that will compare phone numbers. There are 2 phone numbers that are internal numbers which will require no speed dialing because they are internal numbers but all other numbers will require the user to dial a 9 and the number.

    If the numbers are not, then a 9 is needed in front of the number to dial out. Can anyone help me with this formula?
    717332xxxx
    717840xxxx

      • I am looking for a formula (likely an if and then statement) that will state if phone numbers with these first 6 numbers of: 717332xxxx and 717840xxxx are found, we will do nothing but if they are not found in my list of phone number entries, we need to add a number 9 in front of the other numbers for speed dialing.

        So if 717424xxxx is not part of the 717332xxxx and 717840xxxx, I need to add a 9 in front of 717424xxxx.

        This is close below but there may be something I am doing incorrectly because I need it to add a 9 in front of the formula for all other numbers.

        =IF(COUNT(SEARCH({"717332","717840"},A1)),"No Speed Dial",IF(ISNUMBER(SEARCH("other",A1)),"Speed Dial"))

        • Hello!
          Use the ISNUMBER function to determine when the SEARCH function finds a text string.
          Please try the following formula:

          =IF(SUM(--ISNUMBER(SEARCH({"717332","717840"},A1)))>0,"No Speed Dial","Speed Dial")

          Hope this is what you need.

  3. Hello - I am trying to code some events as occurring on workdays (daytime events Monday-Friday, evening events Monday-Thursday) and some events as occurring on weekends (evening events on Fridays, daytime and evening events on Saturdays and Sundays). My dataset has one column with a date in mm/dd/yyyy (column J) and one column with a "time type" - either Day or Evening (column O) that's coded with the timevalue formula.

    I'm able to get a formula that mostly works, but it doesn't capture the fact that Mon-Thurs evenings are workdays.

    For row 279, for example: =IF(AND(WEEKDAY(J279,2)<=5,O279="Day"),"Workday","Weekend")
    *this returns "Weekend" for every single evening event

    I did try an amended formula but it returns inverse results (workday when I want weekend and weekend when I want workday).
    =IF(AND(WEEKDAY(J280,3)<=4,O280="Day",(OR(WEEKDAY(J280,3)<=3,O280="Evening"))),"Weekend","Workday")

    I'm trying to play around with this and would love any advice. Thank you.

    • Hi!
      Here is an example formula for your problem. Read carefully the last paragraph of the article above.

      =IF(OR(AND(WEEKDAY(A1,3)<=4,F1="Day"), AND(WEEKDAY(A1,3)<=3,F1="Evening")), "Workday","Weekend")

      • Thank you so much - all set now, and have a new formula structure to use going forward!

      • Hi What i want is:

        I have two tables

        First Table contains Address of several person from different countries
        Another Table is having different country names like below

        Table A

        Column A Column B
        Alexender, Turkey
        Alexander, China
        Alexander, South Africa
        Alexander, South America
        Alexander, North America

        Table B
        Column A
        China
        South Africa
        Turkey
        North America
        South America

        Now our formulae should value of column Table B from Table A Column A and the matching country name should be reflected in the Column B of Table A like below results in Column B of Table A

        Turkey
        China
        South Africa
        South America
        North America

        Is it possible ?

        • Hi! I’m not sure I got you right since the description you provided is not entirely clear. If you want to get the name of the country from the text, use the MID function to extract the text after the decimal point.

          =MID(A2,SEARCH(",",A2)+2,30)

          You can also use the new TEXTAFTER function

          =TEXTAFTER(A2,", ")

  4. Hi there,

    I am trying to write a nested IF(AND) formula as follows but its not working for me:
    =IF(AND(C23<D23,C23<E23,C23,(IF(AND(D23<C23,D23<E23,D23,(IF(AND(E23<C23,E23<D23,E23)))))))

    What am I doing wrong here please?

    Thankyou, Rosie

    • Nevermind - I worked it out:

      =IF(AND(C27>=D27,C27>=E27),C27,(IF(AND(D27>=C27,D27>=E27),D27,(IF(AND(E27>=C27,E27>=D27),E27,1)))))

    • Hi!
      Please use the formula below:

      =IF(AND(C23<D23,C23<E23),C23,(IF(AND(D23<C23,D23<E23),D23,(IF(AND(E23<C23,E23<D23),E23)))))

      Conditions in an AND statement must be enclosed in parentheses.

  5. Hi

    I've created the following formula below

    =IF(AND(R6=2,W6=99,X6="R"),4,IF(AND(R6=1,W6=99,X6="R"),2,IF(AND(R6=3,W6=99,X6="R"),9,)))

    I would like to add to the formula so that if R6=1,W6 is any number except "99" and X6="P" then it should return a 1, I was trying to use "99" to include every number except 99 but the formula doesn't appear to work.

    Any help would be appreciated.

    Many thanks

    Joel

    • Hi!
      Adds another condition to the nested IF statements.
      The formula below will do the trick for you:

      =IF(AND(R6=2,W6=99,X6="R"),4, IF(AND(R6=1,W6=99,X6="R"),2, IF(AND(R6=3,W6=99,X6="R"),9, IF(AND(R6=1,ISNUMBER(W6),W6<>99,X6="R"),1,))))

      • Thank you so much Alexander, you're a star!

  6. I want to test if a cell (text) in columns B:I aligns with the cell (text) in column A.

    Rules:

    If 1 cell text in B:I aligns with cell text in A, and all other cells in B:I are blank = TRUE

    If >1 cell text in B:I aligns with cell text in A, and all other cells in B:I are blank = TRUE

    If any cell text in B:I does not align with cell text in A = FALSE

    Thank you!

      • Yes - that is exactly what I was after. Thanks so much for your help, Alexander!

  7. Hello

    I am trying to create a formula so that the following information auto populates

    IF D3=1 day, then "Monthly", IF D3=180days, then "Bi-Weekly", IF D3=270days, then "Weekly". Below is the formula I used but whenever I try to input it, it says that I've put too many arguments in one function. Could you please help?

    IF(AND(D3=1),"Monthly","",IF(AND(D3=180),"Bi-Weekly","",IF(AND(D3=270),"Weekly","")))

    When I use the formula without the quote marks
    =IF(AND(D3=1),Monthly,IF(AND(D3=180),Bi-Weekly,IF(AND(D3=270),Weekly)))
    Then it says FALSE.

  8. Hi sir, I would like to compare data between the number percentage
    I'm using this
    =IF(AND(0%<=W143<=10.99%),"Class 1",IF(AND(11%<=W143<=20.99%),"Class 2",IF(AND(21%<=W143=30.1%,),"Class 4",""))))

    So example if my data is 31%, it should be showing me Class 4 instead of blank
    And When i drag to other cell, it all show blank too
    Exp: 19.9% showing blank too instead of Class 2

    Hope you can help me!
    Thanks!

  9. Hi, Please can you help me, I am trying to add this formula, can you help.

    =IF(I7>30,265, IF(I7>100,530,IF(I7>200,795, IF(I7>300,1060,IF(>400,1325,)))))

    regards

  10. IF(AND(LEN(D48)0,LEN(C48)0,D48=C48),"ON TIME",IF(AND(LEN(D48)0,LEN(C47)0,D48>C48),"DELAY",IF(AND(LEN(D48)0,LEN(C47)0,D48<C48),"BEFORE TIME"))), This formula is depend on actual end date
    This formula I used to get status of one process, but now I want to use for overall project if the first process is delay then overall project status will be delay if one process completed and second process is before time then project status will be before time, like that there are 5 process in each project so how can I do it

  11. I've been trying to use the IF+AND Function to know if my Focus Data is equal to the Accepted data, Y/N. However, after manually checking, it always comes up with a No despite it being a Yes instead.

    My formula is =IF(AND(K2=B2:B186,L2=D2:D186),"Yes","No")

    My Focus data are found in Columns K and L. The Accepted Data is found in Columns B and D.

    Note:
    -I already converted the data to all of these cells to values but it is the same.
    -There are duplicate values in the columns except the data found in Column L. Data in Column L are unique.

    • Hi!
      The condition K2=B2:B186 returns an array of 185 TRUE/FALSE values. The IF function does not work with arrays. To determine at least one match of K2 with a list of values, you can use SUM(--(K2=B2:B186))
      If they don’t work for you, then please describe your task in detail.

  12. Hello,

    I'm trying to return a % based on a range. So for instance. If a discount is between 20%-29%, I want it to return a 3% value. If the discount is between 30% - 39%, I want it to return a 4% value and so on. How do I write this statement? What am I doing wrong?

    =IF(AND(J3>20%,J330%,J340%,J3<100%),"5%","")

  13. I am stuck on expanding this IF statement. The below IF - AND - OR works fine, but..
    =IF(AND(OR(A9="Kevin",A9="Nick"),F9>=(--"10:00 AM")),"good","Bad")

    I want to expand the about to add another name to the OR check and a different time

    for example, I would like A9="Joe" with the F9 check to 8:00 am

    I have tried every combination with multiple IF statements but can't seem to expand this check. At some point, I would like to grow this by 8-10 names and 4-5 times.

    Any help would be appreciated

  14. Hi, i have a a scenario for programe "Annaul procedure review", with frequencey 1year and 3 years and five years. Plz suggest me formula for that scenario.

  15. Dear Sir,

    I have a excel file which created by my superior, I tried to understand how the formula works but in a mist of the logic, please see below the formula:

    =IF(AND(ES$2>=$M14519,ES$2<=$N14519),IF(MONTH(ES$2)=MONTH($M14519),$K14519/$Q14519*(ES$1),$K14519/$Q14519*ES$1),0)

    Remark:
    ES$2 = 31 Mar 2021; M14519 = 28 Mar 2021; N14519 = 27 Mar 2022; K14519 = 41600; Q14519 = 365
    ES$1 = 31

    Basically this formula created to work out the fee amount by month accordingly to the lump sum amount and the contract start/end period.

    I am in a mist of the setup of this part "IF(MONTH(ES$2)=MONTH($M14519)" & what is the relationship of the $K14519/$Q14519*(ES$1),$K14519/$Q14519*ES$1) with the first half of the formula? Why K14519/Q14519*(ES$1) appeared twice in the formula?

    Regards

    • Hello!
      It doesn't make any sense that TRUE and FALSE are the same in an IF function. Expression
      IF(MONTH(ES$2)=MONTH($M14519),$K14519/$Q14519*(ES$1),$K14519/$Q14519*ES$1)
      can be replaced with
      $K14519/$Q14519*ES$1

  16. Hello! Can you please assist me on the below?

    I have 4 Cells (B4:E4) consisting of dependent drop-down lists where a final solution is to appear in Cell B5. Until the final solution appears, it always indicates "PENDING".

    Unfortunately, I have run into a couple of situations where I have received the 8,192 character limit error.

    If I have various scenarios that look similar to the below, how could this be written differently to help with my character limit issue? I have attempted practicing with other formulas just on this small set of lines (ex. IF(AND(OR, IF(OR(AND, IFS..), however I cannot make them work but truly I have no experience using those 3 formulas. Forgive me, but you are working with someone who is still learning but definitely tries!

    TO SUM UP THE LINES BELOW....

    CELL B4 - DIFFERENT IN ALL OF THESE LINES
    CELLS C4, D4, E4 - DROP-DOWN'S ALL MATCH IN EACH OF THESE LINES
    CELL B5 (THE SOLUTION, OR WHAT IS 'TRUE') - IS THE SAME IN EACH LINE, EXCEPT THE LAST LINE (DUE TO DROP-DOWN B4)

    THESE ARE ALL DROP-DOWN POSSIBILITIES WHERE BOTH D4="CLEARLY WRITTEN, COMPLETE" & E4="NO" EXIST TOGETHER. WHAT IS 'TRUE' IN CELL B5 IS THE SAME, EXCEPT WHEN DROP-DOWN B4="DB" IS SELECTED. OTHERWISE, I'D SIMPLY WRITE THIS AS:
    =IF(AND(D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.","PENDING")

    =IF(AND(B4="EA",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="HP",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="CP",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="WP",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="FN",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="ML",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="SS",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
    IF(AND(B4="DB",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. SEND MS1 LETTER.","PENDING"))))))))

    Again, these are only a set of lines. If you need more, please just let me know. Here is a list of all drop-down's if preferred.

    CELL B4 (CONSISTS OF INFO A FORM IS RECEIVED WITH) =
    EA
    HP
    CP
    WP
    FN
    MI
    DB
    GE
    SS

    CELL C4 (ASKING IF THE INFO IS ALREADY IN THE SYSTEM)=
    YES
    NO

    CELL D4 (THE INFO ON THE FORM IS or CONSISTS OF... dependent drop-downs based on what is selected in cell B4) =
    ID PROVIDED
    CLEARLY WRITTEN, COMPLETE
    WRITTEN, BUT ILLEGIBLE OR INCOMPLETE
    REASON PROVIDED INDICATING WHY THEY DID NOT INCLUDE
    INVALID IN THE SYSTEM
    NOT PROVIDED
    BOTH MARKED

    E4 (available if C4 drop-down is "YES", asking if the info provided matches what the system currently shows) =
    YES
    NO

    • Note: Line 6 of formula should have indicated B4="MI" (not B4="ML").... sorry about that :/

      • Me again! DISREGARD! I went back through everything and re-tried the very last example above
        ** IF AND OR ..... =IF(AND(OR(B2=$G$1,B2= $G$2), C2>$G$3), "x", "") ** ....

        It worked on my side as:

        =IF(AND(OR(B4="EA",B4="HP",B4="CP",B4="WP",B4="FN",B4="MI",B4="SS"),C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.",
        IF(AND(B4="DB",C4="YES",D4="CLEARLY WRITTEN, COMPLETE",E4="NO"),"UPDATE THE SYSTEM TO MATCH THE FORM. SEND MS1 LETTER.","PENDING"))

        I am not sure what I did incorrectly when trying it earlier on my side, but thrilled right now! Thank you!

    • Hi!
      I didn't quite understand where the error occurred with a large number of characters. But you can reduce the number of characters in the formula if you write a long text in separate cells and make references to these cells in the formula.
      For example, instead of
      =IF(AND(D4=”CLEARLY WRITTEN, COMPLETE”,E4=”NO”),”UPDATE THE SYSTEM TO MATCH THE FORM. DO NOT SEND LETTER.”,”PENDING”)
      =IF(AND(D4=”CLEARLY WRITTEN, COMPLETE”,E4=”NO”),M1,”PENDING”)
      I hope this will help.

  17. I have formulas that I need to combine together.
    D2 = tool size
    We have small, med/lg, and critical. So if the mold is above 351 (med/lg) it pulls information from another sheet and put in corresponding row. If under 350 (small), then it put in that row. We have added the "critical" criteria, so I need the formula to look at tool size and look to see if the tool size listed has a "C" after it. If the tool size has a "C" after it, then it needs to go to the critical row, otherwise it needs to go to small or med/l

    =IF(D$2>351,'Project Readiness'!I40,0)
    =IF(ISNUMBER(FIND("C",D$2)),'Project Readiness'!I40,"0")

    TOOl # 2
    TOOL SIZE 1300C
    small 0
    med/l 22
    critical 22

    =IF(AND(ISNUMBER(FIND("C",D$2)),D$2>351),'Project Readiness'!I40,"0")

    This didn't work because it still pulled in based on size to the med/l and the critical based on the "C"

  18. Hello there,
    Thank you for such insightful site!
    I tried following your web but I still don't really get the logic, and when i tried my formula below, some of the case it's good, but some of the case it said FALSE. I think there's something missing in my formula.

    Input: Row E is every 25th of the month, no matter what day it is
    Wanted Output : I want to create an automatic calendar for every 25th of the month for payroll system.
    The condition is, if 25th of the month is a public holiday or weekends (Saturday, Sunday), it should be moved to H-1 (24th) or the nearest working day.

    What I get right now:

    =IF(COUNTIF($H$22:$H$25,E13)>0,IF(WEEKDAY(EDATE(E13,0),12)>5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),12)-4),IF(WEEKDAY(EDATE(E13,0),12)5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),2)-5),EDATE(E13,0)))))

    This is the description of the formula
    =IF(COUNTIF($H$22:$H$25,E13)>0, [to see If 25th is a public holiday]
    IF(WEEKDAY(EDATE(E13,0),12)>5, EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),12)-4),
    [if the public holiday falls on Sunday or Monday- 6 or 7, then this is to move the date to nearest weekday - Friday]
    IF(WEEKDAY(EDATE(E13,0),12)5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),2)-5),[if no, 25th of the month is not a public holiday but it is on weekend Saturday, Sunday, then this is to move the date to nearest weekday - Friday
    EDATE(E13,0))))) [if the 25th is a workday]

    I hope you understand what I'm trying to say since it is a bit complicated and English is not my first language.
    I would very highly appreciate it if you can help me with this!

    Many thanks, Dahlia

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

      =IF(WEEKDAY(E13,2) > 5,IF(COUNTIF($H$22:$H$25,E13-WEEKDAY(E13,2)+5) > 0,E13-WEEKDAY(E13,2)+4,E13))

      I hope it’ll be helpful.

      • Hi, thank you for replying!

        I tried the formula but sometimes the result is FALSE

        For example, i put the E13 date is Monday, 2nd May 2022 which is a holiday, so it should be Friday, 29 April 2022 but the result written FALSE.

        Also, I have a case if the holiday happened at Monday, 2 May 2022 and Friday, 29 April 2022, can you help me to revise the formula?

        Thank yiu so much for your help!

        • Hi!
          I don't know which days of the week are your holidays. Therefore, if necessary, change the argument of the WEEKDAY function as you need. In this formula, the first day of the week is Monday. Holidays are 6 and 7 days.

          =IF(WEEKDAY(E13,2) > 5,IF(COUNTIF($H$22:$H$25,E13-WEEKDAY(E13,2)+5) > 0,E13-WEEKDAY(E13,2)+4,E13-WEEKDAY(E13,2)+5),IF(COUNTIF($H$22:$H$25,E13) > 0,IF(WEEKDAY(E13,2)=1,E13-3,E13),E13))

          • Hey! Thank you very much again for replying!
            This formula works well, but I'm getting new issue now.

            So, if i want to apply the formula to holiday that falls on Tuesday or other weekdays (except monday) then how i should add the formula but different weekday function?

            • Hi, a little update on the formula so I tried to move here and there, now it kinda work for 3 holidays in a row on the weekdays, but now it can not filter the weekends anymore

              here is the formula:

              =IF(COUNTIF($A$2:$A$18,I26)>0,

              IF(WEEKDAY(I26,12)>=5,I26-(WEEKDAY(I26,12)-4),

              IF(COUNTIF($A$2:$A$18,I26-1)>0,

              IF(WEEKDAY(I26-1,12)>=5,I26-1-(WEEKDAY(I26-1,12)-4),

              IF(COUNTIF($A$2:$A$18,I26-2)>0,

              IF(WEEKDAY(I26-2,12)>=5,I26-2-(WEEKDAY(I26-2,12)-4),(I26-3)),(I26-2))),(I26-1))),I26)

              I want to fix it but I think it makes the formula even harder, again can you please help me the formula?

              Really really appreciate your help!

  19. Hello, I have multiple conditions for calculating faculty workload and can't seem to get my formula right. The original formula was:
    =IF((AD80-40)/2>0,(AD80-40)/2,"--")

    However I need to check some conditions before performing the operation above.
    -If the total workload credits are >= 24
    -And the total contact hours are >= 48
    -Then run =IF((AD80-40)/2>0,(AD80-40)/2,"--")

    Is this possible?

  20. I would like to use the If(And) formula shown in this page but looking at distinct numbers in a large excel like user id's, for example. How could I use this formula to search many rows of data for a specific column?

    I was thinking vlookup with the IF(And) within it but I was unable to get the syntax correct.

    • Hi!
      Sorry, it's not quite clear what you are trying to achieve.
      There are a lot of formulas on this page. Please describe your problem in more detail.

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