Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 112. Total comments: 4573

  1. I have worksheet that lists int exp and payment for multiple leases. I'm manually opening each amtz schedule and inputing these into each column,each month. I would like to write a formula that will say when the lease number in column A matches a lease from one the amtz schedules, and when the date matches, lookup the int exp and return in column O.

  2. =IF(B2>90,"A",IF(B2>80,"B",IF(B2>70,"C",IF(B2>60,"D",IF(B2="absent","F","F")))))

    i want to excel display F for absent also.

  3. I am trying to write a nested formula. The first vlookup works, however the second and third vlookups are not working. Can anyone tell me what i am doing wrong please?

    =IFERROR(IF(AB2="Other",VLOOKUP(M2,'price list'!A$2:$N$1247,13,FALSE),IF(AB2="Sysco",VLOOKUP(L2,'Sysco US Foods Pricelist'!$A$5:$J$214,5,FALSE),IF(AB2="USF",VLOOKUP(L2,'Sysco US Foods Pricelist'!$A$5:$J$214,10,FALSE),0))),0)

  4. Hi,
    i want to write a formula in C1
    column A1 date is less or equal to today date then = Yes
    column B1 date is less or equal to today date then= No
    if both are less or equal to today date then= Both.

    please help me out.
    i tried if & And but not successes.
    Girish

  5. Hi,
    I'm trying to create an If function that would evaluate a score, then based on that score value predict a future test date based off of the date the first test was taken. Here are two examples:

    1.) TESTED: 1/1/2015 SCORE:>70 NEXT TEST:12 months from first date(1/1/2016) 2.) TESTED: 1/1/2015 SCORE:<70 NEXT TEST:6 months from first date(7/1/2016) Do you have any idea how to format this correctly? Every time I try, I end up with either an error, or the result of my text. Thanks!

  6. I am trying to create above formula to find the efforts based upon the inputs from respective columns as referred, but with 1-1 its returning right when i am trying to add multiple conditions its not working for which need help.

    Below Query1 for one condition but the second query tried to build for another condition its returing Medium value everytime.

    Columns: Technology || No. Of Interfaces || Interface Type || Phases || Complexity || Total Efforts

    Query1: =IF(AND(A3="SOA", C3="New", D3="Requirement", E3="Simple", OR(A3="SOA", C3="Enhancement", D3="Requirement", E3="Simple")), B3*'Efforts Summary'!C3, B3*0.6*'Efforts Summary'!C3)

    Query2:=IF(IF(AND(A3="SOA", C3="New", D3="Requirement", E3="Simple", OR(A3="SOA", C3="Enhancement", D3="Requirement", E3="Simple")), B3*'Efforts Summary'!C3, B3*0.6*'Efforts Summary'!C3), IF(AND(A3="SOA", C3="New", D3="Requirement", E3="Medium", OR(A3="SOA", C3="Enhancement", D3="Requirement", E3="Medium")), B3*'Efforts Summary'!D3, B3*0.65*'Efforts Summary'!D3))

    I need to add similary for design, build, test along with complex category overll.

    Appreciate if anybody can provide some help here.

  7. Hi Savetlana,

    when I input 17 or 20 digits in Cell F8 the result should be "Pass" otherwise "Fail"

    also note that I am checking the length of digits in the cell.

    below condition is not showing positive result.

    =IF(OR(LEN(F8)=17, LEN(F8)=20), "Pass", "Fail")

    Regards,
    Khalid

  8. Please give a formula to get answer for the following:
    TDS (Tax deducted from source) 10% for employees below Gross pay Rs.15000 and for other employees 20% ?

  9. Hello,
    I have a question, in my worksheet i have 2 cell where i have to input value "O13 & Y13", now i want an alert to be flashed if a value is entered in O13 and not in Y13 & vice-versa, how can i do it.

    awaiting your earlist reply.

    Regards
    Karan

  10. I am using below

    =IF(OR(AND(LEN(TRIM(F8 = 17)), AND(LEN(TRIM(F8 = 20))))), "Pass", "Fail")

    1. Hello Khalid,

      From your formula, it's difficult to understand what result you are trying to achieve. I have the following suggestions:

      Show "Pass" if cell F8 contains 17 or 20 characters, "Fail" otherwise:
      =IF(OR(LEN(F8)=17, LEN(F8)=20), "Pass", "Fail")

      Show "Pass" if the value in cell F8 is either 17 or 20, "Fail" otherwise:
      =IF(OR(F8=17, F8=20), "Pass", "Fail")

      If you are looking for something different, please clarify.

  11. Hi Team,
    please find below condition is not working. I am trying to if value is 17 and 20 so result should "Pass" otherwise "fail" but value is showing every time "Pass" with wrong input and right value. kindly check and advise on urgent basis.

    =IF(OR(AND(LEN(TRIM(F8 = 17)), AND(LEN(TRIM(F8 = 20))))), "Fail", "Pass")

    Regards,
    Khalid

  12. Hi there,

    I'm trying to calculate how many months a participant stays in a job, based on a start date in one column and in the next column, each cell is either blank (if they're still employed) or has an end date if they lost their job. I'm trying to figure out a formula that will calculate the months between column J (start date) and column K (end date, if there is one)... so that the number of months will show up in another separate column. If there is a blank, I want the number of months to be calculated between the start date and today's date... I tried to combine formulas and came up with what I copy/pasted below, but it's not working... Is this possible and can you help me figure out a formula that will do what I want?

    Formula that isn't working:
    =IF(ISDATE(K2), (YEAR(K2)-YEAR(J2))*12+MONTH(K2)-MONTH(J2), IF(ISBLANK(B1), (DAY(NOW())>=DAY(J2),0,-1)+(YEAR(NOW())-YEAR(J2)) *12+MONTH(NOW())-MONTH(J2), "")))

  13. Hi Svetlana,

    I'm trying to work out commission structures in excel, for example if the fee is less than 10k, then the commission is 10%, if fee is more than 10k but less than or equal to 20k, then 10% of the first 10k + 20% of anythingthing between 10-20k, and then 10% of 0-10k, 20% of 10-20k, 30% of 20-30k etc and 40% of everything above 30k.

    Please help!

    Kind Regards
    Tom

  14. i am trying to output a letter code base on ranges for example if a cell has numbers from:

    -1 to -26 should output text "A"
    -27 to -54 "B"
    55 TO 25 "C"
    24 TO 6 "D'
    5 TO 1 "E"

    for example if a cell has -23 output would be A

    i tried some of the above but often i get EBC various letters together

    1. Hello Joe,

      Try the following formula:

      =IF(AND(A1>=25, A1<=55), "C", IF(A1>=6, "D", IF(A1>=1, "E", IF(A1>=-26, "A", IF(A1>=-54, "B", "")))))

  15. Hi ,

    I Good tools however i need to add more conditions in the same formula but i was not applied

    Mistake Severity One Cases Two Cases Three Cases Four Cases
    Critical 10% 20% 30% 40%
    Hard 7% 14% 21% 28%
    Normal 3% 6% 9% 12%
    Repeated 3% 6% 9% 12%
    Human error 1% 2% 3% 4%

  16. I'm having trouble building the proper if function for what I need and would appreciate any help!

    I'm in need of a formula which adds different amounts based on a SUM amount. The SUM is cell D15.

    Example IF(D15>=0.55, D15+.25)

    My problem is that I need this for several different number ranges, and all in the same cell.

    If D15=25or higher, D15+3
    If D15=15-24.99, D15+1.75
    If D15=10-14.99, D15+1.5
    If D15=7-9.99, D15+1
    If D15=5-6.99, D15+.75
    If D15=3-4.99, D15+.55
    If D15=0.56-2.99, D15+.5
    If D15=0.01-0.55, D15+.25
    If D15=0, 0

    I hope this makes sense. Mostly it is difficult for me because there are so many variables. Any help would be appreciated!

  17. Hi,

    could you please help me with the below:
    =IF(VLOOKUP(N9;zonespecific.loc.!R:S;2;0)<200;"Replenish";"OK")&IFERROR(VLOOKUP(N14;zonespecific.loc.!R:S;2;0);"Not in stock")

    I am trying to nest a iferror function into the if function to highlight with my specified criteria the cell when the if function is not applicable but I am not able to get it right.
    Thank you in advance

  18. Hi
    I'm trying to create a formula to total a cell based on two conditions
    Column D is titled Activity: Running, Cycling (Condition 1)
    Column K is titled Distance: 5, 10
    Column S is titled Company: Paul, John (Condition 2)

    I can calculate the total amount of running and cycling by using this formula =SUMIF(D3:D57,"Running",I3:I57)
    What I want to do is expand this formula to calculate the amount of running with John or Paul only based on Column S.

    Can you help?

    Thanks Paul

    1. Hi Paul,

      To sum cells with several conditions, you need to use the SUMIFS function rather than SUMIF. For example:

      =SUMIFS(I3:I57, D3:D57,"Running", C3:C57,"Paul")

      1. That worked perfectly.. I just copied in your formula :-)
        Thank you so very much you are exceptional.
        Paul

  19. please excel format how could multiply and subtract in the excel formats.

  20. goodevening :D can you help on what is the formula of this?

    A Route is Classified as LONG Haul if the destination is further than 1000 miles From UK and SHORT Haul if less. (Use Absolute Reference)

  21. I am working on the excel sheet in which i have to put the following conditions like
    if gpa is in the range of 3.81- 4.00 then assign the marks 20
    if the gpa in the range of 3.61- 3.80 then assign the marks 10 and so on for the other gpa what is the solution to this kindly any one can help me
    Thanks

    1. Hi Nayab,

      You can write a nested If formula similar to this:

      =IF(A1>3.8, 20, IF(A1>3.6, 10, IF(...)))

  22. Hello
    I would like to know how to use nested loop functions with logical operator AND, using data from columns in different sheets.
    Like for instance, if there has to be condition placed such that if Column D in Sheet1 AND Column D in Sheet2 AND Column D in Sheet3 AND Column D in Sheet4 are true, the result is RANK1 in Column B in Sheet5?
    How to go about it? I hope I have made myself clear.

  23. Hi
    am trying to create a formula in excel 2010
    =IF(C5=10918,(IF(AY5*0.02<10,10,IF(AY5<0,0,AY5*0.02))),IF(C5=14449,IF(D5=0,0,(D5-S5)/1.145)),IF(C5=32466,IF(D5*0.02<10,10,D5*0.02))
    but it is showing too many arguments for this functions, am planing to use more thn 50 ifs here, is it even possible??

  24. Hi,

    I am trying to create a formula that will insert one of four colored symbols. For example if cell value is >= 96% insert solid blue circle, if btwn 93% and 96% insert solid green circle. Etc.

    Thanks for your help!

  25. hi
    have prep this string
    =CONCATENATE(IF(C2=10918,(IF(AY2*0.02<10,10,IF(AY2<0,0,AY2*0.02))),IF(C2=14449,(D2-S2/1.145))),IF(C2=17178,(IF(D2=0,0,IF(D2<230,4,D2*2/100)))),IF(C2=32466,IF(D2=0,0,IF(D20.4,VLOOKUP(C2,'Merchant ID'!A:E,3,0))*D2)
    but am getting
    FALSEFALSE100
    am confused what am doing wrong, help me out?

  26. =IF(D8>D34,5,IF(D8=D34,4,IF(AND(D8=(D34-2)),2,IF(AND(D8=(D34-3)),1,IF(D8<=0%,0)))))

    Id like to correct my previous, This is my actual formula

  27. I've been trying to create formula on this:

    Higher than inflation rate, the score is 5
    Within inflation rate, score is 4
    2 points below inflation rate, score is 2
    3 points or more below the inflation rate, score is 1
    At 0% or negative, score is 0.

    So I entered the following formula, where D8 contains the score, and D34 is where I entered the inflation rate. But it doesn't work.

    =IF(D8>D34,5,IF(D8=D34,4,IF(AND(D8=(D34-2)),2,IF(AND(D8=(D34-3)),1,IF(D8<=0%,0)))))

  28. I'm trying to create 5 new character variables from a set of 5 existing variables (test scores - math, lang, phys, soci, comp). The goal is to create a new variable for each score, based on a set of cutpoints of the test score. The comp variable -- a summary or overall variable -- can be either numeric or character. The two character options for comp are "Incomplete" and "Other", and are valid values when there is a missing score for any one of the the other 4 variables. Let's say that the first row of 5 original variables are in cells A2, B2, C2, D2, and E2. If there are numeric values in each of those cells, the formula would be written: =IF(A2>=270|"Demonstrating"|IF(A2<258|"Emerging"|"Approaching")). Where I'm stuck is in writing the formula for when there is a blank value in one of the 4 test score fields (A2, for example). Can you help?

  29. There are 1000 values in one column (C). I need to populate the value '1', if the value in column (C) is equal to 'A','B','C','D' or 'E'. Else, I need it to populate zero. Basically, my simple formula of =if(C1="A",1,0) needs to be extended to A or B or C or D or E - all in one cell formula itself.

    1. Hi Gurgaa,

      Simply embed the OR function in your formula, like this:

      =if(OR(C1="A", C1="B", C1="C", C1="D"), 1, 0)

  30. Hello dear Svetlana,
    If add one more part that is, if cell5 (total) be between 50 and 59 grade C, between 60 and 79 B, between 80 and 100 grade A (for level1,level2 and level3). if cell5(total be between 60 and 65 grade C, between 65 and 79 grade B, between 80 and 100 grade A.
    your kindness will be highly appreciated.

    1. Hi Gul Mohammad,

      You can use the following nested If functions to display grades in a separate cell:

      =IF(AND(OR(A4="level1", A4="level2", A4="level3", A4="level4", A4="level5"), A5>=80), "A", IF(AND(OR(A4="level1", A4="level2", A4="level3"), A5>=60), "B", IF(AND(OR(A4="level1", A4="level2", A4="level3"), A5>=50), "C", IF(AND(OR(A4="level4", A4="level5"), A5>=65), "B", IF(AND(OR(A4="level4", A4="level5"), A5>=60), "C", "")))))

      1. Hi Svetlana:
        I think you missed one and that is,you have not specified the Grade A for level four and level five. Now how do i continue?
        should I just add like the others?

        1. Hi Gul Mohammad,

          Because Grade A is identical (between 80 and 100) for all the levels, I added 5 levels in the first logical test to make the formula a bit more compact.

  31. If you don't mind one more problem that I faced with, and really disturbs me is that how to solve this of course it is in connection with previous.
    if cell4= level1,level2,level3 and at the same time total(cell5) be greater than 50 pass and otherwise fail and at the same time if total be between 50 and 59 that grade must be C, if between 60 and 79 grade must be B and if between 80 and 100 grade must be A.
    But if cell4= level4,level5 and at the same time cell5(total) be greater than 60, rusult be pass and otherwise fail and at the same time if cell5(total) be between 60 and 65 grade C, between 65 and 79 grade B, between 80 and 100 grade A.
    Thank you so much once again.

  32. Thank you too much for helping us. I will appreciate if you could help me with this function.Suppose if cell4= level1,level2,level3 and at the same time cell5(total score) be greater than 50,the student is pass otherwise fail.
    And again if cell4= level4,level5 and at the same time cell5(total score) be greater than 60, the student is pass otherwise fail.
    It will be your kindness if you could help to handle it.

    1. Hello Gul Mohammad,

      Try the following formula:

      =IF(OR(AND(OR(A4="level1", A4="level2", A4="level3"), A5>50), AND(OR(A4="level4", A4="level5"), A5>60)), "pass", "fail")

      1. Thank you so much dear Svetlana Cheusheva.
        It really helps me.
        I appreciate your helping and also your talent.
        best wishes

  33. Is there any formula for the condition where have to use "OR" more than twice.
    ex:
    A B C D Y=YES
    Y N N Y N=NO

    I want a condition : =if(A OR B OR C OR D ="Y","AGREE","NA")

    i want to use "or" more than twice. Is it possible?

    1. Hello Naveen,

      Of course, it's possible. Like in any other Excel function, you can include up to 255 arguments in an OR statement as long as the total length of your formula does not exceed 8,192 characters :)

      =IF(OR(A1="Y", B1="Y", C1="Y", D1="Y"),"AGREE","NA")

      1. Hi Svetlana,

        For Naveen's query we could also use this formula

        =IF(B1="A","Y",IF(B1="B","Y",IF(B1="C","Y",IF(B1="D","Y",IF(B1="Y","N",IF(B1="N","N"""))))))

        Substituting any other text for "Y" or "N" within the brackets.

        But don't you think that a Table with the two columns - one with the condition and other with the output/result, will help this problem with a Vlookup formula. The table can be expanded without having to expand the formula or make it more complex.
        I have been using this method with excellent results and as an Excel professional I would be glad to know your views.
        Regards,
        Ramki

  34. hi I need help for the following

    BIO Scheme above 35
    Maths Scheme above 40
    Technology scheme above 45
    Arts Scheme above 55

  35. Hi,

    I'm looking for help with a formula:

    =IFERROR(IF(H9>90%,H9*G9,"-")

    H9 is the % Accomplished, G9 is the Value. The problem I am having is I need the % accomplished to max out at 100%

    Thanks

  36. its related to end of service calculation

    as per our policy, first 5 years of service, gratuity will be 1 month for each year( ie 30 days X 5) and next 5 years, it is 1.5 month (5and remaining service period it is 2 months.

    what should be formula which will check 4 year service, 8 year service and 12 year service.

    kindly help me.
    thanks

    1. Hi Krishna,

      Based on the information provided in your post I give below the formula which should work for you:
      A B C D
      2 Years worked Months earned
      3 6 6.5
      4 Cross check calculation
      5 Year split Entitled Months
      6 5 1 5
      7 1 1.5 1.5
      8 0 2 0
      9 Total 6.5

      IF(B25,IF(B210,(5*1)+(5*1.5)+(B2-10)*2,0))))

      Let me know if this worked for you,

      Regards,
      Ramki

  37. Hi Esther,

    If you are looking for a match of percentage ranges and the matching values then a lookup table and Vlookup formula can solve your requirement. I can send you the file with the formula and the information I think you are working with.

  38. Hi. Need help for the following.

    Above 90% 0
    Below 90% to 87% 200
    Below 87% to 85% 400
    Below 85% to 83% 600
    Below 83% 1,200

    This does not work (M11 = Result)
    =IF(AND(M11>=87%,M11=85%,M11=83%,M11<=85%),K11,IF(AND(M1190%,"")))))

  39. I need help in formulating a complicated IF statement.

    So I want a spreadsheet that has one cell that will calculate the correct percentage that is marked with an "X". (However there are five different percentage choices that can be marked are 2%,3%,4%,5% and 6%.)

    I started the following Nested IF formula but I'm screwing it up, maybe I need to use an IF/OR formula but can you please help me?

    =IF(E4="X",(D4*0.02),0,IF(F4="X",(D4*0.03),0,IF(G4="X",(D4*0.04),0,IF(H4="X",(D4*.05),0,if(G4="X",(D4*.06))))

    1. Hi Gabriel,

      I think a nested If formula is the right approach. Try this one:

      =IF(E4="X", D4*0.02, IF(F4="X", D4*0.03, IF(G4="X", D4*0.04, IF(H4="X", D4*0.05, IF(G4="X", D4*0.06, "")))))

  40. Hi, Just need some help in creating the formulas for these conditions. Hope you can help me.

    If Taxable Income is: Tax Due is:

    Not over 10,000 5%
    Over 10,000 but not over 30,000 500 + 10% of the excess over 10,000
    Over 30,000 but not over 70,000 2,500 + 15% of the excess over 30,000

  41. Hi Rebecca,

    Try the following nested If's:

    =IF(A1>4999, A1*15%, IF(A1>2999, A1*12%, IF(A1>594, A1*10%, "")))

    1. Thank you so much for your help!

  42. I'm trying to work on a formula for my new employer but I keep getting an error message. Here is what I need it to do:

    If A1 = $595 thru $2999 multiply by 10%, but if it = $3000 thru $4999 then multiply by 12%, but if it = greater than $5000 then multiply by 15%.

    Please help! Getting frustrated trying. TIA

  43. Hi,

    Please advise if I can use the if condition in vlookup. I want to pull data from reference 3, reference 1 and 2 is blank.

    Please advise.

    Regards,
    Arvind

  44. i have a question regarding the first example: IF/AND formula: =IF((AND(C2>=20, D2>=30)), "Pass", "Fail")

    Does the order in the "AND" segment matter? I.E. if it was IF((AND(D2>=30, C2>=20)), "Pass", "Fail"), would that change the result?

    1. Hi Kevin,

      The order of arguments in AND and OR statements does not matter at all. You can put them in any order and the result will be the same.

  45. Hi, I want to achieve a dollar value and I have created this =IF(F9>2888, "100", IF(F9>1588, "50")). But I have getting 50 and 100 as a text even I try to convert it into number.

    Can help?

    1. Hi Kerine,

      The point is that Excel interprets any value enclosed in double quotes as a text value. So, simply remove the double quotes surrounding 100 and 50, and your formula will work fine:
      =IF(F9>2888, 100, IF(F9>1588, 50))

  46. Thank you so very much for this very helpful / educational posts Ms. Cheusheva.

  47. I want to use If A1 = "82041100 / 82041200 / 82042000" and A2 = "China" then A3 = 150 otherwise 200. Could any one tell me what would be the syntax of the that formula in excel.

    1. Hi Muddassir,

      If you mean A1 contains exactly that text string, use this formula:

      =IF(AND(A1="82041100 / 82041200 / 82042000", A2="China"), 150, 200)

      If you mean A1 contains any of those 3 numbers, use this one:

      =IF(AND(OR(A1=82041100, A1=82041200, A1=82042000), A2="China"), 150, 200)

  48. Sorry i have redo this

    Hi i want to achieve this, Results should be in D5
    Can anyone help me out. Both conditions has to be met.

    if C8 68%>= 74.99% Results in Cell D5=200 if C8 >=75%, D5=300
    AND
    if C9 68%>=75% , then D5=300
    if C8 and C9 <68% then D5=0.00

  49. Hi i want to achieve this, Results should be D5
    Can anyone help me out. Both conditions has to be met.

    C8 68 between 74.99 Results D=200 if C8 >=75%, D=300
    AND
    C9 68%>==75% , D=300
    if C8 and C9 <68% then D=0.00

  50. Hello,

    I am trying to write a IF function in excel but not successful :(

    I want to write a formula for percentage calculation IF the column is an integer. No percentage calculation if the column zero and not an integer.

    Hope to receive your reply. Thanks.

    1. Hi Terrance,

      You can use a formula similar to this (just replace A1*10% with the percentage calculation you want):

      =IF(AND(A1<>0, INT(A1)=A1), A1*10%, "")

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