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 53. Total comments: 4573

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  18. 1)If= first month(1 Jan to 31 Jan) all floors commission 2%
    2)If= second month (1feb to 29 Feb) floor wise commission
    Lower floor - 3%
    Middle floor - 3.5%
    Higher floor - 4 %
    3) if = third month ( 1 March onwards) onwards floor wise commission
    Lower floor - 2 %
    Middle floor - 2.5%
    Higher floor -3%

    Please help how to create formula in 1 cell in excel

    1. Hello Swapnil!

      if you write data -
      A B C D E
      Month 1 2 3 2/10/2020
      Lower floor 2 3 2 Middle floor
      Middle floor 2 3.5 2.5
      Higher floor 2 4 3

      Please try the following formula:

      =INDEX(B2:D4,MATCH($E$2,A2:A4,0), MONTH(E1))

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

      1. sorry I do not understand of this

        1. A1 - Month, A2 - Lower floor, A3 - Middle floor, A4 - Higher floor, B1 - 1, B2 - 2, B3 -2, B4 - 2, C1 - 2, C2 - 3, C3 - 3.5, C4 -4 and so on. E1 - 2/10/2020 , E2 - Middle floor

          1. please help its very urgent

            1. Hello!
              If you want to learn something, try to understand how the formula works, and not ask for help all the time.
              =IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(2021,3,31),3,0))))), "Date out of range")

              1. If you write table
                Column a : name of customer
                Column b : source
                Column c : source name
                Column d : value
                Column e : commission %(2%)
                Column f : need formula this column

                Source type 3
                * broker
                * Ref
                * direct sale

                Commission type
                1)If broker sale 1 product get 2% commission ( value * 2%)

                2 ) if any existing sale his ref through 1 product get amount benefit
                * sale 1nd product deal get 10000 rs
                *sale 2nd prduct deal get 20000 rs
                * sale 3 rd product deal get 30000rs

                And

                3) if direct sale get 0% commission

                Please help me how to merge in one cell (f column) formula

              2. Hello!
                Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

          2. Thank you very much it's working
            but one more problem one Month Condition was changed. Condition is Date of period so formula not working...
            *Date of period
            1 ) 1 Jan 2019 to 2 Feb 2020 -commission all floors 2%
            2 ) 3 Feb 2020 to 29 Feb 2020 - commission floor wise( L, M, H) 3 %, 3.5%,4%
            3 ) 01 March 2020 to 31 march 2021* commission floor wise (L, M, H) 2%,2.5%,3%
            Can you please help .....

            1. Hello Swapnil!
              Please try the following formula:
              =IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(YEAR(TODAY()),3,31),3,0))))), "Date out of range")
              Hope you’ll find this information helpful.

              1. Please can someone help me with an excel formula?
                3 sources of get commision
                1)Comany
                2) Customer ref
                3) Direct sale

                1) If multiple company commission for 2 %
                2)if customer ref commision
                *first ref commission rs 10k
                *Second ref commission 20k
                And
                *Third ref commission 30 k
                3) Direct commission "0%"
                How to get commission formula in one cell

              2. Hello!
                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 specify what you were trying to find. Give an example of the source data and the expected result.
                It’ll help me understand it better and find a solution for you.

              3. And one more condition add
                3 types of sources in this
                X, y, z
                X and z eligible for all condition commission
                But y not eligible for all. Only eligible 2% commission in all types of condition

                Please help....

              4. Formula not working on pending period 01 april 2020 to 31-03-2021 ...working only march 2020 ..
                Third condition not completed.. Please help.

  19. =If(and(A2="A",B2="A" or "B"),"Yes","No")
    Using like this in excel, if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

    1. Hello
      Please try the following formula:

      =IF(A2="A",IF(OR(B2="A",B2="B"), "Yes","No"),"No")

      Hope you’ll find this information helpful.

  20. =If(and(A2="A",B2="A" or "B"),"Yes","No")
    Using like this if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

  21. I need a formula that says if cell "M" is "true" and Cell "N" is "yes" then sum cells Q & T but if cell "M" is "false" and "N" is "no" the cell T but if cell "m" is "false" and cell "n" is "yes" cell T

  22. I have been working on a formula for an movie list of 1500 Dvds, Blu-rays, and 4ks that I'm backing up on 3 HDDs, 1 for each type. Now all the movies have a "type" column C2 which will only be a value "SD", "SD/HD", "HD", "HD/UHD", "UHD". A "on HDD" column I2 which will only be a value "Y", "N", "Y/Y", "Y/N", "N/Y", "N/N". These represent if the movie was copied to hard drive/s (some have read errors), I have 3 more columns 1 for each drive L2 = drive 1 SD, M2 = drive 2 HD, N2 = drive 3 UHD. Now if I have a DVD/Blu-ray movie (SD/HD) the Y/Y part matches the type so if the DVD SD did not make it to hard drive but the blu-ray did it would be "N/Y" my ? Is if I just want a "X" in columns L,M,N if the movie made it to the coralating HDD =IF(AND(OR(C2="SD",AND(I2 ="Y",OR(C2="SD/HD",AND(I2="Y/Y",OR(I2="Y/N", "X","") I know I'm on the right track, I will do that for each drive column changing out "Type" but I need help with the correct way to write out the formula, Please Help, Thanks.

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

      =IF(OR(
      AND(C2="SD",I2 ="Y"),
      AND(
      C2="SD/HD",OR(
      I2="Y/Y",I2="Y/N",I2="X",I2="")
      )
      ),TRUE,FALSE)

      For me to be able to help you better, please describe your task in more detail. Your explanation of the formula is very difficult to understand. It’ll help me understand it better and find a solution for you. Thank you.

  23. =IF((B5+C5)<=25400,"900"(B5+C5)<=29900,"1100"(B5+C5)<=30400,"1300"(B5+C5)<=33400,"1500"(B5+C5)<=35400,"1700",IF((B5+C5)<=37300,"1800",IF((B5+C5)<=41100,"2300",IF((B5+C5)<=44500,"2600",IF((B5+C5)<=50200,"2900",IF((B5+C5)<=51600,"3100",3200))))))
    how can use these conditions in a formula excel doesnot allow morethan 8

  24. if 400 kg = 17.5
    If 2000KG=22
    Based on above assumption how to calculate the unit price for 375KG
    what is the best formula for that ?

  25. I would like to know how to determine the effect of pest infestation on crop using the if statement. I am having trouble writing a formula. Can someone help me please.

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

  26. Hello and thank you ahead of time! Been working on finding a code for my spreadsheet for quite some time now, so I am very grateful for this site! Please help!

    I have two sheets in a workbook. Sheet 1 contains data to be retrieved into Sheet 2.
    Sheet 2!A1 is the "ID Number" to look up in Sheet 1 Column A, and Match or Lookup? if Sheet 1 Column D is "Yes", then return "Purple", if Column E is "Yes" then return "Red",if Column F is "Yes" then return "Blue",if Column G is "Yes" then return "Black"

    The code I am working with and return value in:
    Sheet 2 Cell D4
    VLOOKUP(A1,Sheet 1!A:A,IF(Sheet 1!D:D="Yes","Purple),IF(Sheet 1!E:E="Yes","Red),"IF(Sheet 1!F:F="Yes","Blue),IF(Sheet 1!G:G="Black")))

    Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black

    Hope I was able to explain what I am trying to achieve

  27. =IF($F$24,C6*0.75,IF($F$2>=8,C6*0)))

  28. hoping someone can help
    im trying to sort my lab charges
    if i have in column c I have "cbc" i want column e to have price for cbc which is $8.98
    if in column c i have "bmp" then column e should show $14.56
    etc
    i have about 60 different labs and pricing. what is easiest way to do this so i dont have to manually enter the price each time
    thanks!

  29. I have a worksheet with over 10K lines of data which requires multi IF statements based on different criteria. I can't seem to write or find and IF statement that computes in a range. Example. If the value is above x and below y, multiply by Z. Can you help?

    Thank you

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

  30. I have a spreadsheet with rows of data results for 5 tests that have been done and as such if the test has been done would expect to see either a PASS or a FAIL in each cell. However if one of test in that row is not done then that particular cell would be blank. Now I want to add a column which looks at each entry in the row and if any fails then it will remind the user to add some additional information, have tried using logic but I cannot seem to get it to ignore the cell if it is blank. Thus I am after something to solve the following

    PASS, PASS, PASS, PASS, PASS = No Action required
    PASS, " ", PASS, PASS, PASS = No Action required i.e ignore any blanks
    PASS, PASS, FAIL, PASS, PASS = Add addition Info as it sees a FAIL
    PASS, " ", FAIL, PASS, PASS = Add addition Info, again as it sees a FAIL

    Anyway any help would be greatly appreciated as this is doing my head in...

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

      =IF(SUM(--(A1:E1="PASS"),--(A1:E1=""))=5, "No Action", "Action")

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

      1. Hi Alexander,
        Firstly thanks for the response, much appreciated. Now apologies, my fault as I should have probably mentioned that there were other cells between each of the entries which contains other information as such I need to look at each of these cells individually and then make a collective decision on the action, thus the spreadsheet breaks down as follows:

        Cell 1 = Numeric data, Cell 2 = Time(hh:mm:ss), Cell 3 = Text, Cell 4 = Text & Cell 5 = Result (PASS/FAIL/"")

        This then repeats itself another 4 times as each row has 5 probable tests, thus all I want to do is look at Cell 5 of each test and from those entries ascertain whether there is an action to be carried out for that row. i.e. if they are all PASS or blank then no Action is necessary, however if a FAIL is seen then ACTION is required.

        Hope this makes sense and apologies for messing you about as I know you guys are likely busy.

        1. Hello Andy!
          Change the formula

          =IF(SUM(--(E1="PASS"),--(E1=""), --(J1="PASS"),--(J1=""),--(O1="PASS"),--(O1=""), --(T1="PASS"),--(T1=""), --(Y1="PASS"),--(Y1=""))=5, "NO Action", "Action")

          I hope it’ll be helpful.

  31. I used to make a formulae using the following scenarios;
    > 1,000 amount would appear in the column to all who are non regular status
    > 500 for non regular
    example: If employee is on a non regular status the equal amount due for employee we will are 500.

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

  32. Hi. Please help
    I have 23,000 in income. I need to pay 0% in the first 2,000.
    3% up to 5,000.
    4% from 5,ooo to 10,000
    5% on anything above 10,000
    I need all the calculation in one formula.

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

      =((A1-10000)*0.05)*(A1>10000) + ((A1-5000)*0.04)*(A1>5000)*(A1<10000) + (5000*0.04)*(A1>=10000) + (A1*0.03)*(A1<5000) + (5000*0.03)*(A1>=5000)

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

      1. In other words, we don't pay anything in the first 2,000 and we pay 3% from 2k up to 5K.
        Thank you so much again

        1. Hello Marina!
          Please use the following formula

          =((A1-10000)*0.05)*(A1>10000)+((A1-5000)*0.04)*(A1>5000)*(A1<10000)+(5000*0.04)*(A1>=10000)+((A1-2000)*0.03)*(A1<5000)*(A1>2000)+(3000*0.03)*(A1>=5000)

      2. Hi Alexander. You are awesome. Thanksfor the help. I review and everything looks good except the last part of the formula. That should be 0% anything 2,000 or less and from 2,000 to 5000 is 3%. We are supper close.

        650 ((A1-10000)*0.05)*(A1>10000) correct
        200 ((A1-5000)*0.04)*(A1>5000)*(A1=10000) correct
        150 (A1*0.03)*(A1=5000) review

  33. Hello Hino!
    If cells G15 and G9 contains numbers, it is not quite clear why you get an error. I can suppose that there is a number in one of the cells and the other one contains a space or another non-printing character. Anyway, I recommend using the IFERROR function to process errors in formulas. You can find more info about this function here: https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/
    Alternatively, you may find our Remove Characters tool useful for searching and deleting spaces and other invisible symbols. The tool is available as a part of our Ultimate Suite for Excel. Feel free to install the add-in in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
    I hope it’ll be helpful.

  34. Hi Svetlana
    I want to add two cells (G15+G9) and then use the result in another formula. However if one of the cells is empty I get a #value result. How can I get the result of either one of the cells is empty.

  35. what formula should I write here?
    90-94, with honors
    95-97, with high honors
    98-100, with highest honors
    Please help.

    1. Hello!
      Please use the following formula
      =IF(A1 >= 90,IF(A1 >= 94,"Honor",IF(A1 >= 97, "High Honor", "Highest Honor")),"")
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  36. I have two products in kilograms with different Rates and if I sell my products more than first sell . I want continually minus .like
    Qty Rate Amount Qty Rate Amount Sell qty sell rate Sell amount
    12*15=180,. 10*17=170. Formula cell 15*....=....

  37. Thanks for your providing this is good lesson for me.

  38. Dear.
    In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula

    Regards
    Noby Paul

  39. Dear.
    In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula

    Regards
    Noby Paul

  40. How to write the formula of if cell contains the range ±0.25 then return YES otherwise return NO in the Excel.

    1. Hi Samy,

      Assuming you are comparing cell B1 against A1, the following formula will return "yes" if B1 equals A1±0.25, otherwise "no":

      =IF(AND(B1>=A1-0.25, B1<=A1+0.25), "yes", "no")

  41. Hi..pls help me for the below conditions.
    condition 1: wen entering D4 value =A, then E4 value should be=A
    condition 2: wen entering D4 value =blank, then E4 value should be =blank
    condition 3: if D4A and D4blank, means it has some data. so that time E4 should be "B".
    Give me formula for above condition.
    Thanks..

    1. Hi Praveen,
      If I understand your task correctly, the following formula should work for you:
      =IF(D4="A", "A", IF(D4="", "","B"))

  42. I have a problem where I want to generate a dynamic list based on 2 variables (an odd and a dollar amount) e.g.

    Variable 1 - 1.2
    Variable 2 - $1.00

    I have the following function:
    =IF(countif($E$1:E1,"No")=0,(if(($A$1*C1)>($A$2*4), ($A$1*C1)-($A$2*2), if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8),(if(countif($E$1:E1,"No")=1,(if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8)))),($A$1*C1))))))))

    Cell C1 is the value of Variable 1 * Variable 2. Column E, is if a condition has been met, and an amount has been deducted from the outcome in the list.

    Example of the list:
    1 $1.20 $0.20 Yes
    2 $1.44 $0.24 Yes
    3 $1.73 $0.29 Yes
    4 $2.07 $0.35 Yes
    5 $2.49 $0.41 Yes
    6 $2.99 $0.50 Yes
    7 $3.58 $0.60 Yes
    8 $2.30 -$1.28 No
    9 FALSE -$2.30 No

    I want #9 to be $2.30 * Variable 1, until it hits (Variable 2 * 8). Once it hits that limit, it will then be reduced by Variable 2 * 4.

    There are quite a few more "limits" that I would like to set, but I've reduced them to simplify. Any help or suggestions would be great.

    Thanks.

  44. How to calculate through xl formula dragging for 2000 rows
    Logic.1 Sum first numeric cell starting from A2 at E i.e for row 1 is A2, row2 B2, row 3 C3 and row 4 B4
    Logic.2 Sum from 2nd numeric cell to end
    i.e for row 1 B1:D1 for row2 B2:D2 for Row3 C3:D3 and row4 B4:D4
    - A B C D E F
    1 1000 2000 3000 4000
    2 - 500 600 700
    3 - - 5000 6000
    4 - 9000 7000 8000

  45. I'm trying to embed a MID statement for the answer if true. My forumula isn't working, however: =IF(B2=55,(mid(A2,4,2),B2).
    Any ideas?

  46. Hi,

    if it is in excel workbook in one sheet >95 and in other sheet <95 written there, so how we can take average of that( with sign).
    please help me to resolve this issue.

    Regards,
    Vivek

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

      =IF(OR (AND(Sheet1!A1<95,Sheet2!A1>95), AND(Sheet1!A1>95,Sheet2!A1<95)), AVERAGE(Sheet1!A1,Sheet2!A1),0)

      I hope it’ll be helpful.

  47. Please will you give me the function IF in a cell C3 depending of a value in cell B3 with two values , as follows:
    in cell b3 with only two values conditions TRUE, with B3=1 or B3=3, in order that:
    -for the TRUE two conditions: if B3=1 to give in cell C3 the value effect 5 , and
    if B3=3 to give in cell C3 the value effect 10
    -for the FALSE condition, if B3 has different value of 1 or 3, to give in cell C3 the value effect 0 (zero).
    Thank you.

    1. Hello Dim!
      If I understand your task correctly, the following formula should work for you:
      =IF(B3=1,5,IF(B3=3,10,0))
      If there is anything else I can help you with, please let me know.

  48. Please give me a functio for a cell, as follows:
    in cell b3 with only two value conditions b3=1 or b3=3
    in order that:
    if b3=1 to give in cell c3 the value effect 5
    if b3=3 to give in cell c3 the value effect 10

    thank you.

  49. Please give me a functio for a cell, as follows:
    in cell b3 with only two value conditions b3=1 or b3=3
    in order that:
    than if b3=1 to give in cell c3 value effect 5
    if b3=3 to give in cell c3 value effect 10

    thank you.

  50. Hi,
    I have some data in which both column and row contains lot of data.
    Just want to know that :
    1.Column contains data in yellow colour.
    2.Want to sum each row with yellow colour .
    3.Total of yellow colour data pertains to different different user name.
    4.Hence result should be total of yellow colour data in front of respective user name.

    Kindly help on this.

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