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 51. Total comments: 4557

  1. Hello,
    Just want to say thank you , you doing great job, lord shower grace on you.
    I was writing my problen but suddenly answer clicked, thank you again.
    Thank you so much!

  2. Hi,
    I'm trying to write an "If" statement if 3 variables in different cells match up with each other then "Y" else "N"

    If [A2]=[C2]=[D2]THEN "Y" ELSE "N" ENDIF

    1. Hello Michelle!
      Please try the following formula

      =IF(A2=B2,IF(B2=C2,"Y","N"),"N")

      I hope it’ll be helpful.

  3. I'm stuck, wonder if someone can help me:
    I need ranges-
    if cell value is =1500 and =3000 and =5000 "5000+"

    I know it's where I'm putting the () but I can't seem to get it right?

    1. Hello Cindy!
      I’m sorry but your task is not entirely clear to me. I hope you have studied the recommendations in the above tutorial. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

      1. sorry, it didn't type out correctly
        If h2 is less than 1500, then "<1500", if h2 is greater than or equal to 1500 AND less than 3000, then "1500-2999", if h2 is greather than or equal to 3000 AND less than 5000, then "3000-4999", if h2 is greater than 5000, then "5000+"

        1. Hello Cindy!
          Please try the following formula:

          =IF(D2 < 1500,"<1500", IF(D2 < 3000,"1500-2999",IF(D2 < 5000,"3000-4999", "5000+" ) ) )

          The second way:
          Write your values in columns A, B, C. A1 - 0 B1 - 1499 C1 - <1500 A2 - 1500 B2 - 2999 C2 - 1500-2999, etc. If the final value is written in D1, then the formula for determining the output will be as follows:

          = VLOOKUP (D1, A1: C4,3,1)

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  4. Hello,
    I'm hoping to add a calculated field to my pivot table and need a little help.
    A B C calc field
    1 "A only"
    1 1 "A & B"
    1 "C only"
    1 "B only"
    1 1 1 "All 3"
    1 1 "B & C"
    1 1 "A & C"

  5. Hello, how do write a formula for this? If total is between .01%-.99%, output should be $40, if between 1% and 1.99% - output should be $210. I tried IF and also IF(AND with , but I can't get it to work...help please. Thank you so much!
    0.01% ---> 0.99% $40.00
    1% ---> 1.99% 210.00
    2% ---> 2.99% 240.00
    3% ---> 3.99% 270.00
    4% ---> 4.99% 300.00
    5% ---> 5.99% 330.00
    6% ---> 6.99% 370.00
    7% ---> 7.99% 410.00
    8% ---> 8.99% 450.00
    9% ---> 9.99% 490.00
    10% ---> 10.99% 540.00
    11% ---> 11.99% 590.00
    12% ---> 12.99% 640.00
    13% ---> 13.99% 690.00
    14% ---> 14.99% 740.00

    1. hello Phatima!
      Write your values in columns A, B, C. A1 - 0.01% B1 - 0.99% C1 - 40.00, etc. If the final value is written in F1, then the formula for determining the output will be as follows:

      =VLOOKUP(F1,A1:C15,3,1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  6. Hi,
    Great article!
    In my example below, is it possible to add to the formula so that results in column 'C' would also take into consideration the value in column 'B' and (subtract 10 if 'B'=1) or (subtract 20 if 'B'=2)
    Hope this makes sense, thank you!
    A B C
    1 35 1 150
    2 33 150
    3 40 2 200
    4 41 200

    =ifs(and(A1>=30,A1=38,A1<42),200)

    1. Hello!
      Your formula does not work. I did not understand what result you want to get. If there are already numbers in column C, then they cannot be changed by the Excel formula. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

      1. thanks for the response.
        I didn't realize I pasted wrong the formula
        here is the correct one I use:
        =ifs(and(A1>=30,A1=38,A1=" and "<") from Column 'A' which are size values. Now I want to add additional criteria (Column 'B') which is a quality grade (A,B,C or can be switched to numbers 1,2,3) so the calculated value (price) in column 'C' would be less by 10 if 'B=1', less by 20 if 'B=2', less by 30 if 'B=3', if 'B=blank' leave it as it is.
        Hope this makes sense and thank you for all your help!
        Alex

        1. ifs(and(A1>=30,A1=38,A1<42),200)

          1. I'll put it in words:
            if "A1" is "greater than or equal to" 30 and "less" than 38, than 150, if "A1" is "greater than or equal to" 38 and "less" than 42, than 200
            hope this makes sense as it won't let me paste the formula in the chat correctly.

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

              =IF(B1<>"", (IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,""))) - (B1*10), IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,"")))

              I hope this will help, otherwise please do not hesitate to contact me anytime.

              1. Yes! That's it!
                Thank you sooooo much!
                Appreciate all your help!

          2. ifs(and(A1>=30,A1<38)=38,A1<42),200)

          3. ...Never mind, the forum messages will change the formula every time I submit it.

        2. ...here it is again, for some reason when I publish the comment it changes the formula pasted.
          I'll try again with no "=" sign
          ifs(and(A1>=30,A1=38,A1<42),200)

  7. Hi,

    I order supplies for my company and have created a sheet that has 13 tabs, one for each 4 week period and a Summary tab at the end. On each tab I have:

    ItemOrdered Qty Item# Supplier Cost$ DateOrdered DateReceived QTY/CS

    I need to create a formula where IF a certain item is ordered (say triggered by Item#), THEN, the value in the Qty column is tallied as a sum on my Summary tab. Does that make sense?

    Thanks in advance!
    Matt

  8. Hello,
    I need the following formula:
    if in column A I have the word "Revision", I need to put it in column B but not at same level (-1 level).
    Exemple: if A4=Revision: 2.1--> put all content in B3

    Thank you very much

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

      =IF(SEARCH("Revision",A4,1)>0,A4,"")

      If there is anything else I can help you with, please let me know.

  9. if the 1st row is greater than the other row then the answer is 1
    if the 1st row is lesser than the other row then the answer is 0
    if the 1st row is 0 than the other row is 0 then the answer is 1
    if the 1st row is equivalent to the other row then the answer is 0

    1. Hello Vincent!
      I’m sorry but your task is not entirely clear to me. Do you want to compare rows or cells? What does "row is greater than the other row" mean? Could you please describe it in more detail? Thank you!

  10. I am trying to work out an IF formula for the following. I have a spreadsheet that details stock and whether it is in date, out of date or expiring soon. I need the cell to show whether an item expires one month from today, is in date or out of date. I am struggling to work out the IF formula - this is what I have come up with so far, but am unable to get any further. Any help would be greatly appreciated by this newbee to Excel

    =IF(E3<TODAY()*AND(-365-335),"Exp 1 mth",IF(E3<TODAY()-365,"Out of date","In date"))

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

      =IF(E3>TODAY(),"In date",IF(E3 > EDATE(E3,1),"Exp 1 mth","Out of date"))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  11. HI,
    Please suggest how automate in sheet B
    Sheet A.
    01-05-2020 02-05-2020 03-05-2020
    Branch Cash Transfer Cash Transfer Cash Transfer
    A 200 90 500 152 5165 2132
    B 500 100 700 515 6516 98
    C 700 930 785 515 212 19
    Sheet B.
    Date 02-05-2020
    Branch Cash Transfer
    A ? ?
    B ? ?
    C ? ?

    1. Hello Anoop!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  12. WHAT SHOULD BE FORMULAE CONSIDERING BELOW CONDITIONS:
    - Cell is greater than equal to 5 = P-HOME
    - Cell is less than 5 = HLWP
    - Cell is "0" OR "NULL" OR "#N/A" = LWP

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

      =IFERROR(IF(B1 >= 5,"P-HOME", IF(B1=0,"LVP","HLWP") ),"LWP")

      Hope this is what you need.

  13. How do I extend this formula to retrun "Agency" if employer column does not = J Smith Ltd.
    Employer Source Fomula
    J Smith Ltd Direct =IF(C4="J Smith LTD","Direct")
    ABC Recruit Agency
    XYZ Recruit Agency

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

      =IF(ISERROR(SEARCH("J Smith LTD",C4)),"Agency","Direct")

      Hope this is what you need.

  14. Gud day Sir, please I have a large document that is up to 20 pages & maybe more. I want the heading which is in row 1,2,3,4,5 to show at the top of each page, I used the print title option and it worked perfectly. Now I want the bottom note which is up to eight rows to show at the bottom of each page. How can I make that work?
    Please I need help on that.
    Thanks.

  15. Formula is =IF(OR(AND(G16="D",Q16>300000),AND(G16="V",Q16>300000),AND(G16="I",Q16>1000000),AND(G16="ORC",$L$5>600000)),"Y","")
    It's returning the first two conditions "D" & "V" correctly but not the last two conditions. What am I missing? Please help. Thank you.

    1. Hello Luisa!
      I copied the formula from a blog in Excel and made sure that it works. Write in detail what results you want to get. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

      1. Thank you for your reply.
        What I'm trying to accomplish has several conditions, so if:
        Service column has "D" and total of more than 300K the result should say "Y" for yes.
        OR
        Service column has "I" and total of more than 300K the result should say "Y" for yes.
        OR
        Service column has V and total of more than 1M the result should say "Y" for yes.
        OR
        "H" total column is more than 600K the result should say "Y" for yes.
        I hope this is clear explanation. Thank you so much for your help.

        1. Hello Luisa!
          Based on your explanations, the formula needed to make very small changes

          =IF(OR(AND(G16="D",Q16 > 300000),AND(G16="I",Q16 > 300000),AND(G16="V",Q16 > 1000000),AND(G16="H",Q16 > 600000)),"Y","")

          Hope this is what you need.

  16. Formula is= if(and(T3>3,U3>=9,v3<2,w3="NO",X<25),"eligible","ineligible")

    If result come ineligible then I want to know that due to which column this result came.
    Please guide
    Regards

  17. I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.

    1. Hello SHRIKRISHNA!
      To control how the formula is executed, you can use the Evaluate Formula tool. It is located in the menu on the Formula tab.

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Thanks sir, evaluate formula shows it but I want that this evaluation in column next to result of formula.

  18. Kindly help me to get percentage in multiple amount different percentage ratio
    For example:
    60000 to 100000 = 5.5%
    100001 to 150000 = 8%
    150001 to 200000 = 12%
    200001 and above 15%
    How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.
    Thank you
    Pravin Rupapara

    1. Hello Pravin!
      You may use the FREQUENCY function to calculate the number of values in the particular range:

      =FREQUENCY(B2:B100,F2:F5)

      Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)

      Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)

      Enter these formulas into H2:H6 and you'll get the percentage you need.

  19. Ok so I'm trying to compile a file that shows if a particular person has been called during the week.
    Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.
    Tech ID, Name, Called?
    I then have a weekly review sheet that i need to populate. same column lay out
    So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .
    Can Anyone Advise?

    1. Hello Mike!
      If I understand your task correctly, the following formula should work for you:
      1. To calculate data for several conditions on several sheets, you can use the formula

      =SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))

      2. Use this formula in your condition

      =IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  20. Hi All,

    I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.

    Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5
    Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc
    column 3 has OnBench, InProject,InProject,InProject,OnBench

    I need to list this into other sheet of the same workbook, with the below condition.
    List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.

    Can anybody help with this?

    Thanks a lot in advance.

    1. Hello Surya!
      I recommend using function Vlookup for multiple values/ Read more here.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  21. I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.
    I'm doing the formula on a sheet named 'April!'
    Data is on 'Ian!' sheet
    So column B6:B500 on Ian! will have the date
    Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'
    Please help, thank you

    1. Hello Rachel!
      I’m sorry but your task is not entirely clear to me. I need more details to help you. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Write down what specific criteria must be met so that the value of sheet 2 is written to sheet1. Thank you.

    2. I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!

      =SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))

  22. If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.

    Is this possible?
    Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.
    Thanks for your help! I hope this question makes sense!

    1. Hello Lisa!
      If N/A is a text, not an error value #N/A, then use the following formula to calculate the weight:

      = IFERROR (B1/SUM($B$1:$B$50), 0)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  23. Please help me correct these formulas
    =(F32); IF(F16:F21="0",) - H47 = F32 but if cells F16:F21 = 0 then H47 Should = 0%
    Also I require a formular If C11="good",15; IF C11="Satisfactory",9; IF C11="Poor",0; IF C11="N/A",N/A)
    Thank You

    1. Hello Mona!
      I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. What you wrote is not a formula.
      This formula may be suitable for you, but your explanations are very inaccurate.

      =IF(C11="good",15, IF(C11="Satisfactory",9, IF(C11="Poor",0, IF(C11="N/A",#N/A, "" ) ) ) )

      1. Hi

        Yes, I figured i messed that up really bad and managed to correct it to this
        =IF(C11="Good";15;IF(C11="Satisfactory";9;IF(C11="Poor";0;"N/A"))) which works well,
        the only problem is i dont know how to go about having a formula that ignores a N/A and still work out at 100%. I tried yours now and this just leaves the score as a positive.

  24. I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))
    The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?

    1. Hello Jesse!
      Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions in this article above.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.

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

          =IF(G4="Y", IF(H4="X","Repaired","CORROSION"),"")

          I hope this will help, otherwise please do not hesitate to contact me anytime.

          1. Thank you sir works perfectly.

  25. I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!

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

      =INDEX(C1:H1,1,MATCH("NA",C1:H1,0)-1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  26. =IF(O23="Inactive",0,IF(OR(L23=$L$3,L23=$L$4)=TRUE,$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))

    The last if functions works when on its own, but when added to the previous functions it does not work. I do not see where the issue is: only adding one extra agruement to the formula to say if additional something is "Yes" then please add 150 to the amount.

    1. Hello!
      I'm sorry, I don't quite understand your case based on the details you provided. As for your formula, it should look like the one below:

      =IF(O23=”Inactive”,0,IF(OR(L23=$L$3,L23=$L$4),$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))

      If the formula doesn't work the way you'd like it to, please give me some more details about its conditions, their priority and specify what result you need to get. Maybe although the formula you created works fine, it doesn't match these criteria. If you give me more information about your task and the expected result, I'll be able to find the right solution for you. Thank you.

  27. Hi I need a formula to state this
    In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2

  28. Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3

    1. Hello!
      If I understand your task correctly, please try the following formula:

      =IF(AND(A1 >= 1,A1<= 2),2,IF(AND(A1 > 2,A1 <= 4),3,IF(AND(A1 >= 5,A1 <= 9),5,"")))

      I hope it’ll be helpful.

  29. Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.
    =IF($B$4<$C$1,"")
    actual B4=20 and C1=15(goal)
    If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?
    =IF($B$415, XXX)

    1. Hello Maria!
      If I understand your task correctly, please try the following formula:

      =IF($B$4 < 15,"",$B$4-$C$1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. WOW! Thank you. Here I thought I needed a second IF statement. Thanks so much for your help. Maria.

  30. I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.

    I need to compare three columns and depending the message in those columns I need an out come.

    Data:
    A1 will contain update needed or blank
    B2 will contain coordinator needed or blank
    C2 will contain licensed or blank

    Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible

    If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed

    If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update

    If all are blank then I need D2 to say No Action Needed

    Thanks for any help !

    1. Hello Diane!
      If I understand your task correctly, please try the following formula:
      =IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))
      Hope this is what you need.

  31. In same sheet read the 3 column V to x and match the condition of 25> cases in AC column and return the gift he qualified in AE column.

  32. hi,
    my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?

  33. Hi
    I am using a history database and would like to make it a little more efficient. Its purpose is to code surnames using four characters but when there are fewer than four characters, to fill in any gaps with a full stop; for example KIM would become KIM. If I have a column of surnames, is there a formula that would allow me to identify those with fewer than four code letters and which would add the full stop for me. The current process requires me to check each entry and for a large database it takes an age.

    1. Hello Pail!
      If I understand your task correctly, please try the following formula:

      =IF(LEN(TRIM(A1)) < 4,"less than 4",A1)

      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  34. if AH1=Conus and D1=12, then 17
    if AH1=Conus and D1<12, then 15
    if AH1=Oconus, then 28

    1. Hello Jessica!
      If I understand your task correctly, please try the following formula:

      =IF(AH1="Conus",IF(D1=12,17,IF(D1 < 12,15,"")),IF(AH1="Oconus",28,""))

      1. ok, i need to throw another variable in - can this be done?

        if AH1=Conus and D1=12, AND G1=9820580 or 159384 ....then 17
        if AH1=Conus and D1=12, AND G1=6620363 ....then 15
        if AH1=Conus and D1<12, then 15
        if AH1=Oconus, then 28

        1. Hello Jessica!

          Please try the following formula:

          =IF(AH1="Conus",IF(AND(D1=12, SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,""))

          I hope it’ll be helpful.

          1. WOW. thank you so much

      2. Thank you so much!

  35. Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.

    "Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
    Thanks.

    1. Hello Enity!
      Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:

      =IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)

      But this is just a guess. And I'm not a telepath.

  36. I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?

    1. Hello Shane!
      You did not explain what it means "the range is outside of 10%". Maybe the following formula should work for you:
      =IF((B2-A2)/A2>0.1,"out of tolerance","ok")
      Expression (B2-A2)/A2>0.1 can be replaced with another.
      I hope it’ll be helpful.

  37. Hi all,
    How to get different statement in one cell based on the different date ,
    For example:
    I have 5 document received date and 6 document sent date.
    from that once document sent from my side i need statement automatically "Sent"
    from my side document is pending statement should come " Pending"
    and if i entered any date in completed column statement should come " completed"

    1. Hello Jaybal!
      I’m sorry but your task is not entirely clear to me. Excel cannot automatically indicate the status of a document. How does he know that you sent the document? It’ll help me understand it better and find a solution for you. Thank you.

      1. I will receive document from vendor for review and once documents are okay i will approve that doc, if document rejected they will resubmit.
        In my tracker Approved mean i will enter code "A"
        if document reject i will enter code "C"...
        These sequence will happen more than 7 to 8 times.
        My requirement is Once i entered in C / A code i need result Approved /rejected in any one cell
        Exampple:
        (I need status automatically)
        First time doc received A2= C Status "Rejected"
        2nd time doc received C2= C Status "rejected"
        3rd time doc received E2=A Status " Approved"
        like this i have to check based on the code

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

          =IF(COUNTIF(A2:E2,"A")>0,"Approved ","Rejected")

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  38. Hi I need to do a calculation total. I have a column called Quote/Won with one word in each row. I have a Labour £ column. I need to create a total of all labour revenue won. So if any figure in work labour column is won, the revenue figure shows in the total field. (single combined total field)
    eg.
    Total Labour revenue won £ 200
    Won/Quoted Column---- Work Labour £ Column
    Won £100
    Quoted £150
    Won £100

  39. Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of L1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
    Thanks.

  40. I have this formula =IF((AND($F2>=VLOOKUP($C2&""&F$1,Sheet3!$C$5:$E$1200,2,FALSE),$F2=VLOOKUP($C2&""&G$1,Sheet3!$C$5:$E$1200,2,FALSE),$G2=VLOOKUP($C2&""&H$1,Sheet3!$C$5:$E$1200,2,FALSE),$H2=VLOOKUP($C2&""&I$1,Sheet3!$C$5:$E$1200,2,FALSE),$I2=VLOOKUP($C2&""&J$1,Sheet3!$C$5:$E$1200,2,FALSE),$J2=VLOOKUP($C2&""&K$1,Sheet3!$C$5:$E$1200,2,FALSE),$K2=VLOOKUP($C2&""&L$1,Sheet3!$C$5:$E$1200,2,FALSE),$L2=VLOOKUP($C2&""&M$1,Sheet3!$C$5:$E$1200,2,FALSE),$M2=VLOOKUP($C2&""&N$1,Sheet3!$C$5:$E$1200,2,FALSE),$N2=VLOOKUP($C2&""&O$1,Sheet3!$C$5:$E$1200,2,FALSE),$O2=VLOOKUP($C2&""&P$1,Sheet3!$C$5:$E$1200,2,FALSE),$P2=VLOOKUP($C2&""&Q$1,Sheet3!$C$5:$E$1200,2,FALSE),$Q2=VLOOKUP($C2&""&R$1,Sheet3!$C$5:$E$1200,2,FALSE),$R2=VLOOKUP($C2&""&S$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2>=VLOOKUP($C2&""&T$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2=VLOOKUP($C2&""&U$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2>=VLOOKUP($C2&""&V$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2=VLOOKUP($C2&""&W$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2>=VLOOKUP($C2&""&X$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,2,FALSE),$Z2<=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,3,FALSE))), "", 1)
    It is working normally but it is very long, Is their any way to make it easier.

  41. My apologies if you answered this already. I have a spreadsheet with Monday-Sunday in each column (A-G). State Names in Columns H-O. Unique People names in P column and their phone number in column Q.
    Monday
    Monday

    Monday
    Monday
    Monday
    Monday

    I want to know who is available to travel on Monday to Utah.
    I want the formula to provide me with a list of people and their phone number

    1. Hello Sylvia!
      I think that in your case the simplest and the most convenient way is to make use of Advanced Filter in your table instead of formulas. Please find advice on applying filters on our blog:
      how to add filter, about advanced filter, highlight duplicates

      Set a filter in the table and indicate criteria (what values you would like to see) in the necessary columns.

  42. how to connect two condition under each have some conditions?

  43. I am trying to Populate a Date cell in L7 and I have two cells to choose from T7 and U7. I want L7 to be T7 if T7 has a date if not I want it to be U7 since that will always have a date. The Date in T7 will change due to plan optimization.

    1. Hello Blaise!
      If I understand your task correctly, maybe the following formula should work for you:
      =IF(T7<>"",T7,U7)
      If there is anything else I can help you with, please let me know.

  44. I am trying to get a formula as per condition below:

    Condition 1 - 0<A6<=2
    Condition 2 - 0<B6<=2
    Condition 3 - N/A

    "OK" if criteria as below met.
    A6 B6
    1 1
    1 2
    2 1
    2 2
    N/A 1
    1 N/A
    N/A N/A

    "Not OK" if criteria as below met.
    A6 B6
    1 3
    2 3
    3 1
    3 2
    3 3
    N/A 3
    3 N/A

    Need help. Thanks

  45. Hi, I am looking for a formula to build where if the answer in cell E10 = Yes, then populate information in cell D10. As an example, if Yes, then it would populate YM-SL. The information in column D is various sizes. Or I could create a formula in column E, that if Yes, populate with answer from D. Either formula would be helpful. Thank you

    1. Hello Robin!
      I’m sorry but your task is not entirely clear to me. Do you want to fill in cell E or D? Maybe this is a formula =IF(E10="Yes",D10,"")? Could you please describe it in more detail? Thank you!

  46. hello, iam trying to counts pages based on if formaula, but i am not getting the answerer
    If pages are 1 to 5 my answer should be 1
    if more than 5 pages and less than or equal to 14 should be 2
    If anything more than 14 pages my answer should be 3 - but this i am not getting
    Here is my formula which i applied, can anyone suggest me on this
    =IF(B25,"2",IF(B215,"3")))

    1. Hello Ratheesha!
      Please try the following formula:

      =IF(B2>=1,(IF(B2<=5,1, (IF(B2<=14,2,3)))),0)

      Hope you’ll find this information helpful.

  47. I am trying to create a formula that will calculate the LTV (loan to value) I can use. Here is what I have so far.

    =IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%")))

    However, I want to add another two IF conditions that will increase each percentage by 5% if they have 740+ credit AND $120000 annual salary. Thanks in advance for the assistance!

    1. Hello Yixo!
      If I understand your task correctly, maybe the following formula should work for you:

      =IF(AND(D4>=740000,D5>=120000), IF(D3>=850000, "80%", IF(D3>=500001, "85%", IF(D3<=500000, "90%"))),IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%"))))

      Hope you’ll find this information helpful.

  48. Hi,

    Would the if function work for this?

    Port/City Vancouver Montreal
    Shanghai 27 34
    Shenzhen 28 35
    Guangzhou 35 42

    Example:
    Select: Shanghai, Vancouver
    Time: 27 days
    Select: Shanghai, Montreal
    Time 34 days

    1. Hello Jesse!
      Please try the following formula:

      =INDEX(B2:C4, MATCH("Shanghai",A2:A4,0), MATCH("Vancouver",B1:C1,0))
      or
      =INDEX(B2:C4, MATCH($E$2,A2:A4,0), MATCH($E$1,B1:C1,0))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  49. I want to put this equation in cell H57
    1) if cell I54 value is = or than 20 and than 40, then multiply (I54 X N44)

    so please advise how to right this equation

  50. Hi there,

    I have a total of "overtime and regular hours". I want my regular to show the hours in total cell of OV and RE, but not exceed over 40.
    for example:
    Total of overtime and regular is 32.
    Regular cell show the 32.
    but when the overtime and regular cell is > 40, the regular cell show only 40.

    Can you please help me with a formula?

    1. Hello Aimal!
      If I understand your task correctly, the following formula should work for you:
      in B21:
      =IF(SUM(A1:A20,B1:B3)>40, 40, SUM(B1:B20))

      where A1: A20 is overtime, B1: B20 is a regular time.
      If that's not what you wanted, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

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