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 19. Total comments: 4547

  1. Very useful article, It helped me a lot.
    Thanks!!

  2. =IF(A3=1,"A",IF(A3=2,"B",IF(A3=3,"C",IF(A3=4,"D",IF(A3=5,"E",IF(A3=6,"F",IF(A3=7,"G",IF(A3=8,"H",IF(A3=9,"I",IF(A3=0,"X"))))))))))

    This 1= A 2= B i need to an formula to comnine if 1234 it should be abcd if it is 4321 it should be dcba could you please help me with this

  3. Could you please help me to make a formula that produces a list of each class and the minimum mark in that class in a sheet that have all classes mixed together
    Like if I have all marks of 6A and 6B and 6C and I want a table with one column for class and one for minimum mark in that class

  4. I am attempting to combine these two IF commands into one.

    =IF(O2>=0.2,"",C2)

    =IF(C2="A1","A",IF(C2="A2","AA",IF(C2="A3","AAA")))

    I have tried combining them with AND, but without success. I am stumped!

    Any help would be greatly appreciated.

    1. Hello!
      I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(O2>=0.2,"",IF(C2="A1","A",IF(C2="A2","AA",IF(C2="A3","AAA"))))

      1. Many thanks. That worked. I now see where I was in error, which was including "C2" in the first IF command.

  5. Please Help with below formula, How to use both formula into single statement

    we have 2 condition

    =IF(AND(A1>0,B1="MIS"),"40","20")

    =IF(AND(A1>=0,B1="NRML"),"50","100")

    1. Hi!
      These expressions cannot be combined in one formula. If the first condition returns FALSE, should the formula return 20 or should the second condition be tested?

  6. Good day
    im trying to seperate this list using if formula
    1968
    19230
    2068
    20230
    2168
    4968
    63230
    6568
    66230
    68230
    6968
    101230
    102230
    10468
    and i have tried to use this formula =IF(P6=LEN(4),RIGHT(P6,2),RIGHT(P6,4))
    i want it to look like this
    19 68
    19 230
    how best can i solve this

  7. Hi,

    Having trouble with this..

    =IF(OR(AND(H8=0,F8=100%,K3="FINALED"),AND(H8=0,F8=100%,K3="N/A"),"ready to pay","not ready to pay")

    I'm looking for the same statements to come up whether or not k3="FINALED" OR "N/A".

    How can I make that happen?

    1. Hi!
      I don't really understand what result you want to get, but try this formula:

      =IF(OR(AND(H8=0,F8=100%,K3="FINALED"), AND(H8=0,F8=100%,K3="N/A")), "ready to pay","not ready to pay")

      If this is not what you wanted, please describe the problem in more detail.

      1. Alexander,

        The result I need is either "not ready to pay" or "ready to pay".

        If all 3 logical expressions are not met, my result should be "not ready to pay". If they are all met, then I need the contrary. My issue was getting to the same two conclusions with the exception of k3="n/a" or "finaled".

        The formula you sent did it! Thank you

  8. Hi there are two formulas in the sheet and want to make it single.

    =IF((A10),"Under Process","0")

    I tried below formula but it's not worked.
    =IF(OR(A1>0,"Under Process"),IF(B1<C1,"Claim","Not Claim"),0)

    Thanks

    1. Hi!
      Write what is the second formula. Both of these formulas must refer to the same cells. Otherwise, their association does not make sense.

      1. Yes there are 2 columns Column A and Column B.

        For column A formula is;
        "=IF((A10),"Recd","0")"

        I want to merge the formula and track the value at single cell.

        please advise if there is any other formula bring the result.

        1. Hi!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =IF(OR(A10>0,L10>0),"Recd","0")

        2. For Column B formula is;
          "=IF((L10>0),"Recd","0")"

  9. Hi there, I am trying to match criteria between two columns. In the event that they do match, I want their number values in a third column to be summed for the specific rows only where they are matching in the first two columns.

    i.e. A5 "John" matches B118 "John" therefor i want to sum C5 "450" & C118 "550", giving me a total of 1000 in both D5 & D118.

    I would then also like to add an extra condition where it only needs to match the criteria in an additional columns.

    i.e. let say column E. Both E5 & E118 must be "xyz", else column D will not sum D5 & D118.

    Thanks

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

      =(IFERROR(INDEX($C$1:$C$100,MATCH(A1,$B$1:$B$100,0)),0)+C1) * ISNUMBER(MATCH(A1,$B$1:$B$100,0)) + (IFERROR(INDEX($C$1:$C$100,MATCH(B1,$A$1:$A$100,0)),0)+C1) * (ISNUMBER(MATCH(B1,$A$1:$A$100,0)))

      You can read more about searching values using INDEX+MATCH in this article.

  10. Which formula should I use when issuing rates to water consumers by using different rates?

  11. Dear Sir,

    I need your help on following formula,

    IF A1=A and If B1>26 then rate should be 400, but if A1=B and if B1>26 then rate should be 380
    IF A2=A and if B1>22 then rate should be 380, but if A2=B and if B2>22 then rate should be 360

    please share which formula to do this kind of work with multiple conditions

    Nageshwar

    1. Hello!
      Your terms don't seem to be entirely correct. Can be done at the same time: IF A1=A and If B1>26 then 400 and IF A2=A and if B1>22 then 380.
      All the necessary information to write the formula is in the article above.

  12. =IF(AND(G71="*(G11)*",BC71="ABM*"),BB71,"x")
    =IF(AND(G71="*(G11)*",BC71="ABM*"),"Please schedule interview","x")

    I thought I understood your instructions, but when I tried it... waaaaa
    Column G contains responses on level applying for while column BC contains the specific preferred strand for senior high school level. So that if column G says 'Grade Eleven (G11)' and column BC says 'ABM - Accountancy, Business, and Management' then the text in the BB cell should appear or in another cell I tried making it the sentence appear, "Please schedule interview"

  13. Hi there!
    Looks for assistance with this:
    IF 0.01to 1.00 then value +1
    If 1.01 to 2.99 then value /0.5
    If 3 to 999.99 then value /0.6
    If greater than 1000 then value /0.65

    I made this, but its not quite right.....

    =IF(AND(E10>=0, E101.01, E13.00, E101000.00, E10<1000000.00), E10/0.65, ""))))
    Thanks!!

    1. Hello!
      Please use the formula below:

      =IF(E10 > 1000,E10/0.65, IF(E10 > 3,E10/0.6, IF(E10 > 1.01,E10/0.5,IF(E10 > 0.01,E10+1,""))))

    2. IF(AND(E10>=0, E101.00, E10<2.99), E10/0,5 IF(AND( E10 1000.00), E10/0.6, IF (AND(E10<1000.00, E10<1000000.00), E10/0.65, ""))))

  14. Hi Sir,

    I want one formula for below condition.

    A column is true & weight is less than 150 then ans is 250
    A column is true & weight is more than 150 then ans is "A"

  15. In sheet 1 I have made a table which contains A1=codes; A2=description; A3=amount; A4=Availability.
    In sheet 2 I have another table but I'm trying to type any codes on A1=Codes but A2 wont autofill. What formula do I need for it to recognize and autofill the description linked from sheet 1.

  16. OK, I have two companies, "A" and "B". Each company has vehicles: "PU", "ST", "TK", "TT". Company "A" uses fuel profiles, "001", "002", "003", and "004" (Respectively, "PU" uses "001"). While company "B" uses fuel profiles, "005", "006", "007", and "008". What formula would I use to allow me to, from a drop down menu in column A select company "A" or "B", from column B, from a drop down menu, select vehicle - "PU", "ST", "TK", "TT" and have it auto populate the fuel profile?

  17. I am a new Excel user. I am asking here because I assume I need to use the IF statement with others. I have a Budget worksheet plus 12 (monthly) worksheets logging expenses in three categories (exp codes), all in the same workbook. In the Budget worksheet, how do I tell cell E4 to look at column H of each of those 12 worksheets, and if exp code 1 is in cell H16-H36, than add the amount in cell J16-J36 to cell E4 in the Budget worksheet? Then each month as I log my expenses, the Budget worksheet will update showing how much of my budget I have spent for that exp code. Hope that makes sense. If another post is more appropriate, then apologies, please direct me there. Thanks.

      1. Thank you, Mr Trifuntov. That looks exactly like what I need. I will come back if I get stuck, but thanks for your help. (This website is a new resource for me. I found it with Google, and have bookmarked it.)

  18. If I have 100 individuals with unique names and each person is assigned a set of serialized equipment that they need to be accounted for while traveling, however I need to be able to move them from vehicle to vehicle on my roster, how can I create a IF, THEN that will fill in the cells according to their name.

    Example

    Smith James Charles has tool box 234383 and laptop HP2175 and widget 7777348. Today James is in vehicle 1103 but tomorrow he is going to be in 3415. I don’t want to have to copy and paste all his info into multiple sheets, I want the sheets to recognize “Smith James Charles and then prefill all the cells accordingly.

    Is this possible in Excel?

  19. Hey,
    can I not use a condition for range in nested IF's. For example, I have a column with loan amount and I am classifying them into Low, High and Very-high as per the amount. So my Formula is :

    =IF(1100 <G2< 10000,"Low Charge", IF(10001<G2<20000,"Medium Charge", IF(20001<G2<40000,"High Charge", IF(40000<G2," Very High Charge", "N"))))

    So, I am trying to say that the the cell containing the Loan amount (G2) falls between a range, then spend a specific value.
    But the result I get is always "N", which is the output when the condition is not true.
    Please Check. Thank You

  20. Hi,
    Formula for this one please..
    I want that if a number is > or = 60000 the result will be 900 but If the number is below 60,000 it will be multiplied by 3% of that number

    I done this one.. the number above and equal to 900 is correct but below 60000 , it gives 3% answer, ( not the 3% of that number as expected)

    IF((OR(DS10>=60000,DS10>60000)),"900","*3%")

  21. Hi all.
    Please help.

    Column A would need a wild card to look up the word ROLL. column B would have a size e.g 2000 and column C would have a quantity.

    Basically I need if A1 contains "roll" then if B is greater than 2000 but less then 3000 to return the value in C1 (qty).
    Thanks

  22. Hi, I am using these two formulas in two separate cells in excel and they work fine, however I would like to use them in one cell using maybe an IF OR formula or any other formula.

    =IF(H310%,100-(H3-10)*2)

    The first formula cell returns 100% accurately and the second formula cell returns FALSE which is also accurate, however can i combine both formulas in one cell to give me the answer for either?

    Thanks in advance

  23. Hello There,

    I am trying to create an if statement where if two cells are not equal a calculation using cells within the same sheet is provided.

    if M12-L12=0, give me cell E12, however if they are not equal give me (M12-L12)*E12.

    Are you able to assist? It would be very much appreciated.

  24. Hi

    I have a formula and sometimes gives me the right answer, but in some cells give me (FALSE)

    =IF((BN10>BV10),IF(AND(BN10>BW10),IF(AND(BN10>BX10),IF(AND(BN10>BY10),IF(AND(BN10>BZ10),IF(AND(BN10>CA10),"Not Aligned","Aligned"))))))

    1. Hi!
      I can't validate the formula because it uses data I don't have. Also, I can't guess what conditions it should check.

  25. =IF(G4<-81.07,1.2,IF(G4<=-136.18,1.1,IF(G4<=-170.67,1,IF(G4<=-205.16,0.9,IF(G4<-239.65,0.8)))))

    Kindly help I'm trying to equate losses with the specified targets what could be the issue?

  26. Please help.
    my journal
    A B C D E
    L or S Entry TP SL No of units
    (IF A = "S" then B - C and if C = 0 then D - B) IF A = "L" then C - B and
    if C = 0 then B - C)
    I hope Im clear.
    Thank you.
    Art

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

  27. Hey there,

    I have a table with two columns I would like to compare:

    Column A: On account (either Yes or No)

    Column B: Deposit (if account status is Yes, then this should be blank, but if account status is No, there should be a deposit amount)

    I would like to highlight the cells in column B that are blank AND account status is No. This will highlight all of my non-account files that require a deposit.

    Hopefully, this makes sense!

  28. What's wrong with my formula.

    =IF(G2>=104.53,"1.2",IF(G2>=95.82,"1.1",IF(G2>=87.1,"1.0",IF(G2>=78.4,"0.9",IF(AND(G2>=1,G2<=69.69,"0.8"))))))

    1. Hello!
      Please try the following formula:

      =IF(G2>=104.53,1.2, IF(G2>=95.82,1.1,IF(G2>=87.1,1, IF(G2>=78.4,0.9, IF(AND(G2>=1,G2<=69.69),0.8,"")))))

  29. I want to do TDS calculation, kindly help me for this
    01) if Column M2- Section 194 J and Column K2 is Alphabet - P then Result 10% and
    if column M2 - Section 194 C and Column K2 is Alphabet P then result 1% and
    if column M2 - Section 194 C and Column K2 is Alphabet F then result 2%
    if column M2 - Section 194 C and Column K2 is Alphabet C then result 2%
    if column M2 - Section 194 I and Column K2 is Alphabet P then result 2%

  30. I'm trying to track task priority based on due dates

    IFS(OR(H4=TODAY()+21),"High","Low","Medium")

  31. Hi I'm trying it do an If AND Or formula
    Situation If carrier1 is James and Mode is water then Yes otherwise Jo But if carrier is Harry and Mode is Road then Yes. Otherwise No. Harry will only ever be Road but James can be different modes.

    My formula is showing
    =IF(AND(OR(W7="James", AG7="water"), OR(W7="Harry")), "YES", "NO")

    1. Hello!
      Please try the following formula:

      =IF(OR(AND(W7="Harry", AG7="water"), W7="James"), "YES", "NO")

  32. Hello Im creating a excel worksheet i need a little help,

    Exchange Rate
    K1=1
    K2=1.30

    Result in: E1
    IF A1 dropdown "CAD" and B1 Dropdown "CAD" - then Cost C1*K1
    IF A1 dropdown "USD" and B1 Dropdown "USD"- then Cost C1*K1
    E1= Calculation + D1

    Result in D1
    IF A1 dropdown "USD" and B1 Dropdown "CAD" - then Cost C1*K2
    IF A1 dropdown "CAD" and B1 Dropdown "USD" - then Cost C1/K2
    D1=Return with calculation amount else return with 0 (if above conditions aren't met)

    How can i do this please help

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(A1=B1,C1*K1,IF(AND(A1="USD",B1="CAD"), C1*K2,IF(AND(B1="USD",A1="CAD"),C1/K2,0)))

  33. If I have
    column A Column B
    25
    25
    25.25
    24.5
    54
    54
    54

    and in column B I want to calculate 'If A2="25,54", then plus 2 otherwise +6. I want to isolate specific number to plus 2 and the rest plus 6.
    I tried = IF(A2=25, A2+2, A2+6) but it only applied for 25 to plus two, but the rest of the number is plus 6. How do I get the 54 to plus two too?

    1. Hi!
      Your conditions contradict each other. What is "the rest of the number"? What result do you want to get exactly?

  34. Trying but not succeding....
    Trying to make a calculation based upon a letter in a cell. I have a single calculation figured out, but i want to add another condition.
    Here is my original formula: =IF(M35="Y",F35*70%,F35*65%)

    Now I want to add if M35=D, then multiply by 100% and keep the rest above also.

    2. OK, after relentless searching online, I finally figured it out.

      This is what I put: =IF(M35="Y",F35*70%,IF(M35="D",F35*100%,F35*65%))

      It works, so I'm happy.

  35. Hello!

    how can I get the formula for this,

    I want to show the output of this available,discontinued,N/A
    become like this available,Out of Stock,Not available in daily update,

    The discontinued will be Out of Stock and the N/A will be Not available in daily update.

    Thankyou for helping me.

    1. I think what you wanna say is to change the way a formula express such as:
      Discontinued-->Out of Stock
      N/A-->Not available in daily update
      You can customize your own expression by using "things that you wanna say" in the formula.
      example were in the content above.

      hope this helps.

  36. Good evening,
    Would you please help me with Formula? i will greatful.
    value A (input number)
    value C (standard value)
    Value B 100.

    if value A is less than 55% of value C then output 0
    if value A is between 55% to 99% of vale C then output (80% of B)
    if value A is 100% or 110% of value C then output (100% of B)
    If value A is 100% to 120% of value C then output (120% of B)

    1. Hi, Sir Alexander! I am new to excel. Just wanna ask if how will my IF function go if for example, there is one class session per week. One session costs $100. And there are 5 students at max. In the left row, the teacher will indicate Present or Absent. But there are times in a session where not all 5 students are present. Eg only 3 are present, $100 will be divided to only 3 who are present. The cells on the right (after Present/Absent column) should be in numerical form. Thank you, Sir! Hope you can help me with this case..

      1. Hello!
        Use the COUNTIF function to count the number of students present. Then divide 100 by that number.
        I hope it’ll be helpful. If something is still unclear, please feel free to ask.

    2. Hi!
      The tutorial above contains answers to your question.
      You can use this formula:

      =IF(A1/C1 < 55%,0,IF(A1/C1 < 99%,B1*80%,IF(A1/C1 < 110%,B1,IF(A1/C1 < 120%,B1*120%,""))))

  37. Hello thank you for this article and the further examples provided in the comments.

    I have a Bonus with 3 criteria (KPIS) attached. They get full bonus if all three criteria/ KPIs are met. The bonus is reduced if any of the 3 criteria are not met. Criteria 1 and 2 are worth 10% and criteria 3 is worth 5%

    e.g.

    if target is met bonus is £500

    I will enter the data against the 3 KPIs that show if each one is met or not. if all three are met bonus would remain at £500 if KPI 1 is not hit the bonus will reduce by 10% £50 and be £450, if KPI 2 is not hit the bonus will reduce by 10% (of the original £500) so another £50 and KPI 3 would be £25

    Any combination of KPI1, KPI2 and KPI3 is possible, so there are 9 scenarios I think. for example it would be possible for KPI 1 to not be met but 2 and 3 be met.

    I would like the bonus figure to be calculated automatically.

    I cannot work out how to write this, please help!

    1. Hello!
      The formula below will do the trick for you:

      =B1-B1*((A1=0)*0.1+(A2=0)*0.1)-(A3=0)*25

      where A1 A2 A3 - KPI
      B1 - bonus (500)

  38. Hello Alexander,

    I'm trying to use an IF statement to return the expression "HD" in case the index cell I'm looking for has any of the values: A, B, C, D, E, F, G
    To better explain this I started with the following formula:
    IF(INDEX(January22;N7-13;$C$5)=(????);"HD";" ")
    Where I wrote "????" in the formula is where I tried to use an OR statement to check the values A, B, C, D, E, F, G but it doesn't work.
    Can you help me with this one?

    Thank you in advance!

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets.
      Here is a formula that compares a cell with a set of characters.

      =SUM(--(A1={"A","B","C"}))>0

  39. Sir,
    I have querry where column A has two names and column B has one name but if the name in column is a part of column A the in column C only the non same name should come

    IN SHORT Common Name Part or Full should be removed

    A (COLUMN) B (COLUMN) C (COLUMN)
    SHAILESH SINGH SINGH SINGH
    RAMESH PATEL PATEL PATEL
    SURESH SHAH SURESH SURESH
    ANAND SHAH ANAND ANAND

    Please Guide
    Thanks
    Shailesh

      1. Thank you Sir, I has solved my big query.

  40. Hi!

    I am trying to create a fairly complex IF formula and am having difficulties, any guidance would be appreciated!

    I want the cells this formula is in to fill in as 1SD, 2SD, or 3SD based on the range of 3 different sets of cells. I am able to get it to work with one and two arguments but get the "Too many arguments" error when I attempt to add the third one in. I wasn't able to get it to work by nesting the IF arguments either but I'm likely doing it incorrectly. I have tried nesting the IFs and just get the "This formula has errors" box.

    Working with two arguments:
    =IF(OR(AND(Q14>=D13),(D13=D13),(D13=D13),(D13=D13),(D13=D13),(D13<=R16)), "1SD", "2SD", "3SD")

    Thanks in advance!

    1. Hello!
      I am not sure I fully understand what you mean. What is the meaning of the condition (D13=D13) ? Why repeat it 4 times?
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

      1. Apologies, Alexander, that definitely pasted incorrectly!

        Here is the formula I've been trying to get work:
        =IF(OR(AND(Q14>=D13),(D13=D13),(D13=D13),(D13<=R12), "1SD") but am having difficulty getting the IF arguments to nest properly.

        So, if we take the data as follows:
        the value entered into cell D13 is 3.000
        based on the values in these range of cells:
        1SD: Q12 = 0.232, R12 = 1.762
        2SD: Q14 = -0.532, R14 = 2.526
        3SD: Q16 = -1.297, R16 = 3.291
        Then G13 (the cell containing this formula) would fill in as 3SD since the value of 3.000 falls into that range.

        Also, the values in the Q and R columns change based on the values entered into column D; they are not constant.

        Thanks!!

        1. Hi!
          Have you tried the ways described in this blog post?

          =IF(AND(D13 > Q12,D13 < R12),"1SD",IF(AND(D13 > Q14,D13 < R14),"2SD",IF(AND(D13 > Q16,D13 < R16),"3SD","")))

          Please note that the value 1 is suitable for all 3 ranges

          1. This worked beautifully, thanks!!

  41. how do I write a formula for the following:

    I need a my formula to count as 1 if the following criteria are met in two different columns within a different worksheet.

    so in worksheet2 if the word Apples - Red appears in column A and the date in column G is older than 30 days count as 1.

      1. Thanks it returns a blank cell but should return 7, I'm wondering if the Today formula is correct, I need it to count if the date (are all different dates) entered anywhere in column G are 30 or more days old not 30 days from today.

        If my worksheets have names and I need to only include data from A2 to A300 is the below formula still correct:

        =IF(AND('Data Sheet 4 - Fruit!'A2:A300="Apples-Red",'Data Sheet 4 - Fruit!'G2:G300-30>TODAY()),1,"")

        Also if the criteria isn't met could it return a 0.

        Thanks

        1. Hi!
          If you need to find the difference between dates, use the second date instead of the TODAY() function. If you want to get 0 in case of a negative result, replace "" with 0 in the formula.
          Please read carefully the guide I linked to.

          1. Alexander Thanks I have read the guide so many times but I'm so confused and its still not returning the result it should.

            I don't need a count of dates between each other I need the formula to count how many Apples - Red in column A2:A300 and if any dates in column G2:G300 that are older than 30 days, basically I need to count how many Apples-Red are past their use by dates by more than 30 days??

              1. But if I use Today()-30, won't it count the dates that are 30 days less than today? If so I actually need the formula to count how many of the dates listed are out of date by 30 days i.e.

                Column G has:
                Used by dates
                20/3/2021
                31/12/2021
                30/12/2020
                4/5/2021
                1/2/2021
                4/2/2022
                etc

                I need to know of the dates in column G how many are over their used by dates by 30 days and I need the formula to count how many in total.

              2. Hi!
                The formula I sent to you was created based on the description you provided in your first request. If you need to consider dates that are 30 days less, use the <= operator

                "< ="&TODAY()-30)

  42. Data is in A1= 100. (this is fix data ).

    B1-B10 = date wise high data will come
    C1-C10=. Date wise low data will come
    D1= Pass/Fail (fix Data)

    1.
    Now I want if D1= Pass then if B1 to B10 >= A1+20. Then Result "CLEAR" BUT C1 to C10 <A1-20

    2.

    If D1=Fail then if C1 to C10< A1-20. Then Result "Fail" That time B1 to B10 <A1+20

    3- this Rule should be applied Row wise. If CLEAR Result come first then CLEAR will be valid Output after that if Fail condition met in next row then Fail is invalid in this condition and Results should not chnge to Fail.

    Same For If Fail came first after that Clear Result is not valid.

    1. Hi!
      Unfortunately, I couldn't understand your description of the problem. Write an example of the source data and the result you want to get.

  43. I need help writing =if formulas.
    I have the following'
    A B C D E
    ID age flag
    83125782 63 1 63 Final
    82343216 63 1 63 Final
    82059889 63 1 63 Final
    82843001 56 1 56
    82843001 34 1 56,34 Final
    82843001 15 15
    82843001 13 13
    86835004 61 1 61
    86835004 14 1 61,14 Final
    83326997 58 1 58
    83326997 51 1 58,51
    83326997 19 1 58,51,19
    83326997 17 1 58,51,19,17
    83326997 13 1 58,51,19,17,13 Final
    85120571 32 1 32 Final
    82225798 47 1 47 Final
    82488841 54 1 54
    82488841 48 1 54,48
    82488841 15 1 54,48,15
    82488841 10 1 54,48,15,10 Final

    My formula for D is =IF(AND(A1=A2,C2=1),D2&","&B2,B2)
    and formula for E is =IF(OR(A2A1,AND(C21,A2A1))=TRUE,"Final","")

    I want to essentially concatenate the ages for each series of ID only if they have a value of 1 in C.
    and when I have all the ages for a particular ID write Final to the last concatenated ID in the series.
    There are multiple ID's as well as single ID's.
    Thank you.

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

      =IF(AND(A1=A2,C2=1),E1&","&B2,B2)

      =IF(OR(AND(C1=1,A2<>A1),AND(A2=A1,C2<>C1)),"Final","")

      I hope I answered your question. I

      1. Hi Alexander,
        I put the wrong data.

        A B C D E
        ID age flag
        83125782 63 1 63 Final
        82343216 63 1 63 Final
        82059889 63 1 63 Final
        82843001 56 1 56
        82843001 34 1 56,34 Final
        82843001 15 15
        82843001 13 13
        86835004 61 1 61
        86835004 14 1 61,14 Final
        83326997 58 1 58
        83326997 51 1 58,51
        83326997 19 1 58,51,19
        83326997 17 1 58,51,19,17
        83326997 13 1 58,51,19,17,13 Final
        85120571 32 1 32 Final
        82225798 47 1 47 Final
        82488841 54 1 54
        82488841 48 1 54,48
        82488841 15 1 54,48,15
        82488841 10 1 54,48,15,10 Final

        What I need to do is concatenate the ages in Column D, as long as the ID (Column A) are the same and there is a "1" in the Flag (Column C). The first 3 rows have different ID's but starting at row 4 through 7 the ID's are the same but for row 6 and 7 the flag is blank so I don't want to concatenate the ages for those rows.
        In addition when ID's change and all the ages have been concatenated I want to write the word "Final" on column E. I hope this time the explanation is better.
        These are the formulas you sent.
        I believe I need to use more IF's and AND's conditions to test for different scenarios.

        =IF(AND(A1=A2,C2=1),E1&","&B2,B2) This one is working fine
        =IF(OR(AND(C1=1,A2A1),AND(A2=A1,C2C1)),"Final","") This one is not giving me the right results.

        Thank you!

        1. Hi!
          Your attentiveness could help save a lot of my and your time.

          =IF(AND(A1=A2,C2=1),E1&","&B2,IF(C2 < > 1,"",B2))
          =IF(OR(AND(C1=1,A2 < > A1),AND(C1=1,A2=A1,C2 < > C1)),"Final","")

          Hope this is what you need.

  44. I have finally got the following formula to work

    =IF(F13=45,F13<=100),"Top Dress","")

    is there a way to do it differently to not have the repeated "topdress" if both if statements are true

    1. Hi!
      The formula is written incorrectly and cannot work. You can write your terms like this

      =IF(AND(F13=45,F13<=100),"Top Dress","")

      But the first condition does not make sense, since it is always true if the second condition is true.

      =IF(F13 < = 100,"Top Dress","")

      1. Ugg.. thanks so much for the response, but just realized the beginning was missing.

        =IF(F13=45,F13<=100),"Top Dress","")

        could you please take another look? I appreciate the help

  45. Hi there,

    Thanks for this article :).

    I have been trying to add a formula where if the H cell result is greater than 900, it will add 450 as a number, or a 0 if less than 900.

    =IF(H651>900,"450","0") - This is the formula here. It is working but it seems to be formatting the 450 or 0 as text instead of a number. Therefore, other formulas don't add up the total amount correctly.

    Thanks for this, this is for an earnings sheet for commissions.

  46. Ahi, Alexander

    Can you help me with the formula for the following

    If the amount is less than Rupees 21001 to 75001 then 0

    Kindly do the needful

    1. Hi!
      Please re-read the article above, it covers your case completely. Try this formula

      =IF(AND(A1>21001,A1<75001),0,A1)

  47. Hi Mr Trifuntov,

    How do i formulate let say if A1 has =55, any value in A1 will stay.

    i.e. A1=55 to 65 then B7 shall have the actual value of A1.
    A1>=66, then B7 will show the actual value of A1

    I cant get around the use of a correct excel formula. I need your advice. Thank you

    2. correction:
      A1=65, B7 will show the value of A1 "as-is"

      Again, thank you

  48. i'm facing a dilemma, i'm working at a leasing company, and the official reports that i take from our equivalent of the DMV about our vehicle count and status and plate No. comes in Arabic, i want to transfer the arabic character to the english character, because they're using and english letter against an arabic one, for example A always is the first letter of the arabic alphabet, but using CTRL+f and replace is a time consuming process if you do 3 times a week as this report shows which vehicle do have and don't have a person authorized using the vehicle to avoid making my company take violations and force the actual user to pay it, this report make us avoid these kind of issues, do any knows any formula or another way to speed up the process? as macro isn't the way i tried several times in various ways but with no luck.
    The characters transfer chart as below:
    A=أ
    B=ب
    D=د
    E=ع
    G=ق
    H=ه
    J=ح
    K=ك
    L=ل
    N=ن
    R=ر
    S=س
    T=ط
    U=و
    V=ي
    X=ص
    Z=م

  49. I have a data where i Wanted to return “On Track” or “Medium” or “High”
    based on
    if A3 = “Submission” & if Y3 is >4 then Z3 is “On Track”
    if A3 = “Submission” & if Y3 is <3 then Z3 is "Medium"
    if A3 = "Submission" & if Y3 is 2 then Z3 is “Medium”
    A3 = “Implementation” & if Y3 is <=2 then Z3 is "High"
    if A3 = "Testing" & if Y3 is <=1 then Z3 is "High"

  50. Greeting to all of you,
    I have face to bellow formula
    Age Age category
    7 10-14
    2 >5
    3 >5
    6 6-9
    7 10-14
    8 10-14
    11 15-19
    13 15-19
    14 15-19
    19 20-49

    =IF(B45",IF(B4<=6,"6-9",IF(B4<=10,"10-14",IF(B4<=15,"15-19",IF(B4<=20,"20-49",IF(B4<=50,"<5"))))))

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