Comments on: IF AND formula in Excel

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. Continue reading →

Comments page 4. Total comments: 490

  1. Hi! Please i have two sheets in my workbook and the first sheet contains setting which want it to reflect in my second sheet. This is how I want it, I have products and number of products in setting sheet and have created a table with define name. So i now validated the table in the second sheet. I want any product i select in the validated cell also display the number of products. Below is the formula which does not want to work proper for me. Thank You =IF(C5=Settings!C3,Settings!D3,IF(C5=Settings!C4,Settings!D4,IF(C5=Settings!C5,Settings!D5,IF(C5=Settings!C6,Settings!D6,IF(C5=Settings!C7,Settings!D7,IF(C5=Settings!C8,Settings!D8,IF(C5=Settings!C9,Settings!D9,IF(C5=Settings!C10,Settings!D10,IF(C5=Settings!C11,Settings!D11,IF(C5=Settings!C12,Settings!D12,IF(C5=Settings!C13,Settings!D13,IF(C5=Settings!C14,Settings!D14,IF(C5=Settings!C15,Settings!D15,IF(C5=Settings!C16,Settings!D16,IF(C5=Settings!C17,Settings!D17,IF(C5=Settings!C18,Settings!D18,IF(C5=Settings!C19,Settings!D19,IF(C5=Settings!C20,Settings!D20,IF(C5=Settings!C21,Settings!D21,IF(C5=Settings!C22,Settings!D22,IF(C5=Settings!C23,Settings!D23,IF(C5=Settings!C24,Settings!D24,IF(C5=Settings!C25,Settings!D25,IF(C5=Settings!C26,Settings!D26,IF(C5=Settings!C27,Settings!D27,IF(C5=Settings!C28,Settings!D28,IF(C5=Settings!C29,Settings!D29,IF(C5=Settings!C30,Settings!D30,IF(C5=Settings!C31,Settings!D31,IF(C5=Settings!C32,Settings!D32, "Not in Store"))))))))))))))))))))))))))))))

  2. I have a cell that displays the current date using the NOW function. I would like to run a formula that multiplies an existing cell value by 1.03 when the Now function = 01/01/2024.

    1. Hi! Create the date you want using the DATE function and use it in the condition in the IF function. For example,

      IF(NOW()=DATE(2024,1,1),A1*1.03,"")

      Since the NOW function returns the date and time, I would recommend using the TODAY function to compare with the date.

  3. I am creating an order form and I am using data validation.
    I need the customer to enter a value equal to or greater than 3024 and in multiple of 36
    I have been using =MOD formula, how do I add the equal to or greater than?

  4. Hi,

    I have 2 columns of data, 1 with gender (F or M) & one with a score. Is is possible to write a formula that identifies those with a specific gender AND score? Can all females with a score of <16 and all males with a score of <27 be identified as "good" for example?

    Thanks for you help

  5. Good morning!
    I'm having trouble making the formula that i want within the excel formulas if you are able to give some insight it would be appreciated, or be able to tell me what i might possibly be doing wrong, the logic in my head to make the formula is this :
    =IF(AND(A1:A75=C1:C75,B1:B75>D1:D75), "Greater", "Less Than")

    I'm trying to compare two pivot tables, i need any column that matches the same name ie: any value from A1:A75 equals to exact values from C1:C75 then compare the same row from B1:B75 against D1:D75.

    I'm trying to have it do: if A1 = C1 and B1 > D1, greater, less than. but since it's a pivot table that changes, i can't get it to work properly. Any insight would be appreciated, Thank you!

      1. If i extra the data from the sheets, is there a way i can compare values from A1 against C1:C75 that will show that value of B1 and then repeat that for A2,A3,A4...

        I'm trying to do it within an if statement but i think that I'm mistaken as I'm trying "=IF((A1=C1:C75),B1, "Not on list")", but if i try to do a similar input to A2 then it is giving me a "spill"

        Thank you again and sorry for multiple questions.

      2. Sad to hear,
        Thank you very much for the response!

  6. Hi, good day to you sir.

    The information is really helpful.
    I would like to ask about the formula, this is correct?

    IF(AND(POLA=1,TARGET/REALISASI>=120),"120%",
    IF(AND(POLA=1,TARGET/REALISASI=120),"120",
    IF(AND(POLA=2,REALISASI/TARGET<120),(REALISASI/TARGET)*100%))))

  7. Hi, how can I make the formula below work?
    Can I combine IF with IF AND

    example:
    =IF(C2<90,"A",IF(AND(B2100),"APPLE","OTHERS"))

    1. Hi! Have you tried the ways described in this blog post? Based on your description, it is hard to completely understand your task. I don't know what you want to do, but maybe this formula will work:

      =IF(C2<90,"A",IF(B2>100,"APPLE","OTHERS"))

  8. Hi

    I am trying to create formula that will give a true or yes (I don't mind which) result if in 3 cells, in different columns, certain codes are present. I have read these pages as thoroughly as I can but I think because I want it to look for more than one code it is not giving a 'true/yes' outcome when the conditions are met. This is the formula I have tried:

    =IF(AND(OR(AB8="GD*",AB8="EX*"),(OR(AU8="GD*",AU8="EX*")),(OR(CH8="GD*",CH8="EX*"))),"YES","NO")

    I also tried:
    =IF(AND(AB2={"EX*","GD*"},AU2={"EX*","GD*"},CH2={"EX*","GD*"}),"YES","NO")

    I need it to come back true if the cells contain a code starting with EX or GD. If all 3 cells do then it should return true/yes. It doesn't have to return anything if the conditions are not true, I don't mind having a blank cell.

    Thank you for your time.

  9. I need a formula to calculate tax slab First 100,000 is 0% Tax Next 350,000 is 25% Next 2,050,000 is 30% Excess of 2,500,000 is 35%
    I have achieved to get the correct result upto 30% Tax slab but I m failing to add the last slab of 35%
    =IF(O8<100000,0,IF((O8-100000)<450000,(O8-100000)*0.25,87500+((O8-450000)*0.3)))
    Kindly assist me

    1. Hi!
      Here is a sample formula that you can use:

      =MIN(C2,100)*0% + MIN(MAX(C2-100,0),350)*25% + MIN(MAX(C2-350-100,0),2050)*30% + MAX(C2-2500,0)*35%

  10. I have an excel file with the following columns (MINS, HOURS FREQUENCY, AVERAGE WEEKLY HOURS), and I am trying to write a formula to calculate the average weekly hours based on an average 12 week period, depending on the answer in the frequency column. Can anyone help with a nested formula

    If frequency = Daily, multiply hours by 5 (based on working days)
    If frequency = weekly, multiple hours by 1
    if frequency = monthly, divide hours by 4
    if frequency = quarterly, divide hours by 12

    Thanks
    Hazel

  11. If the employee is single deduct 15% tax from the basic salary.
    If the employee is married deduct 12% tax from the basic salary.
    Whether single or married deduct 3% tax for each dependent.

    I need help please 🙏🙏

    2. i think your question is incomplete because you have mention that employee single deduct 15% and married deduct 12% it is understanding but the next condition u have written that either single or married deduct 3% it not possible because u already mention in question about that so i think this question is wrong

  12. I have a master workbook with Employee Names listed in Column A

    I want it to look at the Employee Years of Service workbook, find the Current Year Column and return that number

    Example of Employee Years of Service Sheet

    23 24 25 26 27 (Year)
    Employee 1 5 6 7 8 9
    Employee 2 1 2 3 4 5
    Employee 3 1 2 3 4 5
    Employee 4 3 4 5 6 7
    Employee 5 12 13 14 15 16
    Employee 6 7 8 9 10 11

    Example of what I'm looking for in the "Master"

    Employee 1 (Find Employee 1 in the Years of Service Workbook, Find the Current Year and return a 5)

  13. 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

    1. 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.

      1. Thanks a lot! Very helpful!

  14. 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?

  15. 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.

  16. 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?

  17. 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"," "))))))

    1. 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.

  18. 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!

  19. 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

    1. 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.

  20. 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.

      1. Thank you!!! That worked perfectly!

  21. 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!!

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. You can also find useful information in this article: Nested IF in Excel – formula with multiple conditions.
      Try the following formula:

      =IF(NOT(ISBLANK(A1)),E1*0.98, IF(AND(NOT(ISBLANK(B1)),ISBLANK(A1)),E1*0.97, IF(AND(ISBLANK(B1),ISBLANK(A1),NOT(ISBLANK(C1))),E1*0.97,"")))

  22. 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

    1. 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.

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

  24. 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.

  25. 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.

      1. That's great. Thanks. Much appreciated.

  26. 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

    1. 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.

  27. 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"

  28. 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.

  29. 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

  30. 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?

  31. 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.

    1. 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.

      1. Alexander,

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

  32. 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"

    1. Hello!
      To count criteria, use the COUNTIF function.

      =IF(COUNTIF(A1:A7,"")+COUNTIF(A1:A7,"No")=7,"Not Completed", IF(COUNTIF(A1:A7,"N/A")+COUNTIF(A1:A7,"Yes")=7, "Completed",""))

      This should solve your task.

  33. 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

      1. Thank you for the quick response! This works- you are amazing!

  34. 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

      1. 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"))

        1. 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.

  35. 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.

    1. 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")

      1. 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 ?

        1. 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,", ")

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

  36. 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

    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.

    2. 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)))))

  37. 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

    1. 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,))))

      1. Thank you so much Alexander, you're a star!

  38. 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!

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

  39. 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.

  40. 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!

  41. 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

  42. 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

  43. 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.

    1. 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.

  44. 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%","")

  45. 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

    1. Hello!
      You can find the examples and detailed instructions here: Excel IF statement with multiple AND/OR conditions, nested IF.

      =IF(AND(OR(A9="Kevin",A9="Nick"),F9 > = TIME(10,0,0)),"good",IF(AND(A9="Joe",F9 > = TIME(8,0,0)),"good","Bad"))

      Instead of nested IF you can use the new Excel IFS function.

      =IFNA(IFS(AND(OR(A9="Kevin",A9="Nick"),F9>=TIME(10,0,0)),"good",AND(A9="Joe",F9>=TIME(8,0,0)),"good"),"Bad")

      I hope my advice will help you solve your task.

  46. 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.

  47. 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

    1. 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

  48. 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

    1. 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.

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

      1. 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!

  49. 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"

    1. Hi!
      Your task is not completely clear to me. Explain what it means "go to the critical row" and "go to small or med/l".

  50. 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

    1. 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.

      1. 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!

        1. 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))

          1. 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?

            2. 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!

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