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 2. Total comments: 307

  1. What is wrong with my formula? I've tried it multiple ways, and I keep getting errors:
    =
    IF(AND(L13="PM",E13=.5)),4,
    IF(AND(L13="PM",E13>=.35)),6,
    IF(AND(L13="PM",E13>=.45)),8,
    IF(AND(L13="EST",E13=.3)),4,
    IF(AND(L13="BOTH",E13=.25)),4,
    IF(AND(L13="BOTH",E13>=.3)),8,
    IF(AND(L13="BOTH",E13>=.35)),10,
    IF(AND(L13="BOTH",E13>.45)),12%)))))))))))

    1. Hi! Check the formula below, it should work for you:
      =IF(AND(L13="PM",E13=0.5),4,
      IF(AND(L13="PM",E13>=0.35),6,
      IF(AND(L13="PM",E13>=0.45),8,
      IF(AND(L13="EST",E13=0.3),4,
      IF(AND(L13="BOTH",E13=0.25),4,
      IF(AND(L13="BOTH",E13>=0.3),8,
      IF(AND(L13="BOTH",E13>=0.35),10,
      IF(AND(L13="BOTH",E13>0.45),12%))))))))
      If you have a lot of conditions, to avoid errors, I recommend using the IFS function. Read more: The new Excel IFS function instead of multiple IF.

  2. I'm trying to write an IF formula that will give me results as such for each additional year in service:
    Year 1 = 80
    Years 2-6 = 120
    Year 6 = 128
    Year 7 = 136
    Year 8 = 144
    Year 9 = 152
    Year 10 = 160

  3. I have tried to write an IF AND formula that will give me the results of the given criteria below.

    Criteria 1(A1) Criteria 2(B2) Result
    <500,000 0-25 Small
    3,000,000 0-50 Large
    >3,000,000 51-100 Mega

    =IF(AND(A1<500000,B1<26)"Small","Medium") works. But when I try to nest other IF AND it does not work. I was trying to use IF AND two criteria as shown and nest IF AND with three criteria. Like =IF(AND(A1<500000,B1=500000,A1<=3000000,B1<26)"Medium","Large"))). This did not work. Will IF AND work for this? What is the best formula to use as to capture all the results above?

    1. Hi! You can find the examples and detailed instructions in this guide: Nested IF AND statements. Based on this information, the formula could be as follows:

      =IF(AND(A1<500000,B1<26),"Small",IF(AND(A1<3000000,B1<50),"Large",IF(B1<100,"Mega","")))

      1. I have a scenario where I tried to rank customers as Large, Medium, Small, and Informal. If cells A3 and B3 contain values for a customer for 2021 and 2022 respectively. The condition for “Large” is both A3 and B3 being greater than or equal to 5,000,000, or A3 being greater than or equal to 5,000,000. The condition for “Medium” is both A3 and B3 being greater than 1,000,000 but less than 5,000,000, or A3 being greater than 1,000,000 but less than 5,000,000. The condition for “Small” is both A3 and B3 being greater than 300,000 but less than or equal to 1,000,000, or A3 being greater than 300,000 but less than or equal to 1,000,000. The condition for “Informal” is both A3 and B3 being less than or equal to 300,000, or A3 being less than or equal to 300,000.

        A little adjustment to my earlier question.

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

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

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

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

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

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

    1. Hi! To find a sum with multiple conditions, use the SUMPRODUCT function. To determine the date in a cell, use the ISNUMBER function because the date in Excel is written as a number.

      =SUMPRODUCT((G1:G10+B1:B10+E1:E10)*ISNUMBER(C1:C10)*ISNUMBER(F1:F10))

      I hope my advice will help you solve your task.

  8. 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"?

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

      1. Ah. Thank you thank you thank you!!!

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

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

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

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

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

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

  16. I am creating an order form and I need the cell to only allow 240 or greater and in multiples of 20. Is it possible to do a nested formula in data validation that will do this?
    I use the =MOD(F64,20)=0 formula for other cells in data validation

      1. HI,
        This formula =AND(MOD(F64,20)=0,F64>=240) worked great! Thank you so much!

        Am I able to add a function to add other cells to meet the 240 minimum requirement?
        Examples:
        F64 = 240 (still needs to be in multiples of 20)
        or F64+F66 = 240 (still needs to be in multiples of 20)
        or F64+F66+F68=240 (still needs to be in multiples of 20)
        or F64+F66+F68+F70=240 (still needs to be in multiple of 20)

  17. Hi!

    I'm trying to count up from 1 (2, 3, 4...), starting in column A2 (continuing down A3, A4, A5, A6....). However, I only want the counting up to proceed if two conditions are met, those being that B2 and C2 both = 1. So counting up will continue only if those are met, otherwise, I want the numbers to remain the same until the conditions are met.

    The problem is, I cannot get the following equation to work, as it only seems to return the FALSE value, and I can't find any examples of how to format a formula in a "Value if true (or false" field for the IF(AND) scenario.

    Here's the equation:
    =IF(AND(B3="1",C3="1"), A2+1, A2)

    Any advice will be appreciated!

      1. The revised IF function worked, thanks Alexander, much appreciated!

  18. can you help me to make a formula for
    1f less than 100000 commission 0.03%
    100000 to 199999 commission 0.04%
    200000 to 500000 commission 0.04%
    more than 500000 commission 0.05%

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)