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 17. Total comments: 4549

  1. Is there an excel formula where I want that if the birthdate (Column C) is equal to 12 and is elementary level (Column G) then it will returns to the age (Column E)otherwise returns to "Underage"(Column E).

  2. Hi I need help with a nested if argument, I want to calculate a specific percentage according to an alphabetical code, all entries with codes (column F) R,P,B,W need to depreciate by 5% per year (column H) if the code is anything else the percentage is set at 50%

    This is what I have so far:

    =IF(or(F10="r",f10="p",f10="b",f10="W"),and(f10=n),100%-(H10*5)/100),"50%")

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

      =IF(OR(F10="r",F10="p",F10="b",F10="w"),H10*0.95,H10*0.5)
      or
      =IF(SUM(--(F10={"r","p","b","w"}))>0,H10*0.95,H10*0.5)

  3. Hi,

    I'm trying to work out a formula which deducts travel time from appt time. Column 'P' is actual Appt duration, Column U is overall Appt duration (inc travel) I have the following formula which deducts actual appt time from overall appt time but wanted to include an IF statement saying take column 'U' if it = 00:00 (no travel time) so currently have the following: =IF(U135="00:00",U135,IF(U136<P136,P136-U136,U136-P136))

    However this isn't working as still calculates regardless of column 'U' being 00:00, any idea what I'm doing wrong?

  4. Hello,

    I am trying to use an "if and" statement with 3 conditions at the end of a very long statement.

    It is 3 IF statements, 1 IF AND statement, and 1 IF AND AND statement. The formula works up to the 3 IF statements + 1 IF AND statement, but does not work once I add the IF AND AND statement. I have copied below what I have so far, can someone please assist?

    IF(V2="*",S2,IF(S2="",T2,IF(S2="*",S2,IF(AND(S2="",T2="<"),"T2","U2",IF(AND(AND(S2="",T2=""),U2,V2))))))

    Above is translated to if V2="*", then look at S2. If S2 is blank, then look at T2. If S2="*", then use S2. If S2 is blank and T2 = "<", then look at T2, if not then look at U2. If S2 is blank, and T2 = "", then look at U2 if not then look at V2.

    1. Hi!
      Your conditions are incorrect. If condition IF(AND(S2=””,T2=”<”) returns FALSE, then there are 2 choices: 1) U2. 2) checking the condition IF(AND(AND(S2="",T2="”)

  5. Hi,

    I require formula for a confiriton where, "If we input date in one cel, then a specific amount should popup in the destination cell"

    Example: if a cell A15 has date 23/04/2022, then cell G15 should popup with the amount 50.

    will this be possible?

  6. Dear Sir,

    I noticed you are helping a lot of people here and i would like to ask if you could guide me in the right direction in excel. I am trying to create a worksheet with available lens (of all sorts) for our lab. And separate worksheets with lenses grouped for uses (contact lenses/ lenses by brands and stuff like that). My idea was for the lens name with its ID from the other worksheets to be copied into the availability worksheet (like this ='Contact Lenses'!D8). But when there is a date of taking the lens in the availability worksheet the worksheet that regards that specific lens (like 'Contact Lenses') will have in the relevant cell a value of Yes (for taken) and a No value for when there is a date of return in a separate column in the 'availability' worksheet. However The problem i am struggling with is that the same lens may occur several times in the same availability worksheet - making If statements kind of ridiculous.

    Things like started coming out as such - which obviously won't work
    'IF(OR(AND('Availability' $C10:C100 ISTEXT = "CL1.75 #A" , 'Availability' $F10:F100 ISBLANK), AND('Availability' FC10:F100 ISTEXT = "CL1.75 #A", ISTEXT )), "Yes", "No")

    ('Availability' worksheet) - Cells[Lens ID, date of taking item, name of person who took it, date of returning item]

    Example 'Lens' worksheet - Cells [Lens ID, Specification, Available?]

    I hope i explained it well enough. Do you have an idea on how to proceed with such a task?

    Regards
    Wiktor

    1. Hello!
      If you need to find the last match with Lens ID in the ‘Availability’ worksheet, you can use the XLOOKUP function with Search_mode = -1
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  7. I am trying to get my IF formula to work with multiple conditions. Here are the conditions:
    1. Cell C4 must contain a certain term, let's call it "apple".
    2. Cell M4 must contain a numerical value.
    If both conditions are met, I would like to subtract the value in cell M4 from the value in cell D4.
    If one or both of the two conditions are NOT met, I would like to return the value "N/A"

    Thanks in advance for your help!
    Mike

    1. Hi!
      To check if a cell contains a number, use the ISNUMBER function. Also, use the recommendations from the article above.

      =IF(AND(C4="apple",ISNUMBER(M4)),D4-M4,"N/A")

      This should solve your task.

      1. Thank you Alexander, this worked well. I was not employing the ISNUMBER function, but I had the rest of the formula elements. I really appreciate the swift response.

  8. I have. project and I'm struggling to know how to do this IF formula given what they say:
    "The dataset contains ten measurements of student readiness for college in columns B-K. The description of each one of them is available in the file."
    "To analyze this data, you need to create additional columns and recode the data, making low a 1, moderate, a 2, and High a 3 (you can use an IF function to do this). In this format, the highest the number, the more prepared the student is."

    the column already says moderate low and high what function would I do to get it to formulate to 1,2,3, etc...

  9. Sorry not all the context was stated in my message, The results , need to be
    Poor (345)

    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(J2 > = 5,"top",IF(J2 > 3,"good","poor"))

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

  10. Hi Alexander, I am hoping you can help me, its probably a simple formula, I just can't seem to get my head around.
    I have a list of scores ( results) in one column, from these scores I need to determine, poor ( 345). preferably the result being the corresponding text.

    Average Total Output
    2.3
    2.4
    2.5
    2.6
    2.7
    2.8
    2.8

    This is my formula so far: =IF(J2>=5,"top",IF(J23,"good",IF(J2>33>0,"poor",)))) but its not giving me the result I am looking for. Help appreciated.

  11. Hello

    Please assist me with this scenario:

    Bus fare from A to B - $30 for the elderly
    Bus fare from A to B - $50 for adults
    Bus fare from A to B - $40 for teenagers
    Bus fare from A to B - $30 for kids

    Bus fare from B to C - $35 for the elderly
    Bus fare from B to C - $55 for adults
    Bus fare from B to C - $45 for teenagers
    Bus fare from B to C - $35 for kids

    I receive trip sheets with passengers' dates of birth and destinations so I want create a formular that says if a passenger is 13 years old, and travelled from B to C, they should have paid $45

    1. Hello!
      Pay attention to the following paragraph of the article above - Using multiple IF statements in Excel (nested IF functions). It contains answers to your question.
      You didn't provide enough information to write you a formula.

  12. Good afternoon,

    I need two different formulas for percentage increase/decrease in one cell.

    For more clarification...I am compiling statistics. July 2020 has 0 (crime reported) and July 2021 has 1 (crime reported).

    I used this formula: =IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4) and the percentage is 0%.
    I need to show the 100% increase from 2020-2021.

    So I used this formula : =if(iserror((L23-L4)/L4),1,(L23-L4)/L4) and the percentage changed to 100%.

    Some months have say 5 (reported crimes) in 2020 and 13 (reported crimes) in 2021. For those, the formula =IF(ISERROR((D34-D15)/D15),0, (D34-D15)/D15) works.

    Is there a way to combine both formulas for each cell in order to give me an and/or result?

    Thank you kindly for your response.

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =if(ISERROR((L23-L4)/L4),1,IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4))

      I hope it’ll be helpful.

  13. Hello. I need to combine two functions and need help please.

    two cells H177 and H65

    when H77 is <=0,2 i want to calculate the abs difference and if is 0.05 ok, if not Failed
    =IF(AND(H177<=0,2;ABS(H177-H65) 0.2 it responds as Failed,

    but how can i combine
    when H77 is >0,2 ABS((H177-H65)*100/H77)<20);"OK";"FAILED")

    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(H177<=0.2,ABS(H177-H65),IF(ABS((H177-H65)*100/H77)<20,"OK","FAILED"))

  14. Hi, I am trying to do the following:
    If c2 is blue (true or false) then c3=c2 - 8

    If c2 is another colour nothing happens

    So.. is it possible to bind c3 to a c2 in that way and still be able to write any number in c3 if condition is not met?

    Thanks

  15. I have a list of prices. "X" and "NON-X" products (one column). I want the price separately to the "X" Column and "NON-X" Column from that mixed list..

  16. Hi,

    I am trying to create a code for trading.

    C3 is Australian Dollar
    D3 is Euro
    bull is up
    bear is down
    cons is consolidating/sideways

    There is 8 different combinations for the answer that can be seen in the function below.

    =IF(AND(D3="bull";C3="bear"); "bull";"") IF(AND(D3="bear";C3="bull");"bear";"") IF(AND(D3="bull";C3="bull"); "cons";"") IF(AND(D3="bear";C3="bear"); "cons";"") IF(AND(D3="bull";C3="cons"); "bull";"") IF(AND(D3="bear";C3="cons"); "bear";"") IF(AND(D3="cons";

    1. Hello!
      If I understand the problem correctly, you have many combinations of conditions in two columns. I recommend writing down all possible combinations in a table and using the VLOOKUP function to search for the desired option. Here is a guide with examples: How to Vlookup multiple criteria in Excel.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you for the quick response!

        I digged into VLOOKUP function and I am not 100% sure how to use it in my instance. I will try to further explain my situation.

        Basically I have 8 possible combinations and 3 possible outcomes, but just one right answer.

        First word is one currency (Australian Dollar)
        Second word is the other currency (Euro)
        Answer is what their cross currency should be if conditions are met (EURO / Australian Dollar)
        bull + bull = cons
        bear + bear = cons
        bull + bear = bear
        bear + bull = bull
        cons + bull = bull
        cons + bear = bear
        bull + cons = bear
        bear + cons = bull

        There can only be one right answer for each date.

        1. Hi!
          Write your criteria in the G2:H9 range as:
          bullbull cons
          bearbear cons
          bullbear bear
          bearbull bull
          ......

          Formula in cell C2:

          =VLOOKUP(A2&B2,G2:H9,2,FALSE)

          Please use the link I gave you earlier.
          I hope I answered your question.

    2. Here is the full function

      =IF(AND(D3="bull";C3="bear"); "bull";"") IF(AND(D3="bear";C3="bull");"bear";"") IF(AND(D3="bull";C3="bull"); "cons";"") IF(AND(D3="bear";C3="bear"); "cons";"") IF(AND(D3="bull";C3="cons"); "bull";"") IF(AND(D3="bear";C3="cons"); "bear";"") IF(AND(D3="cons";C3="bull"); "bear";"") IF(AND(D3="cons";C3="bear"); "bull";"")

  17. Hi, I have 2 columns of data, both containing either a 0 or a 1.

    I need to be able to code the cell in another column to read the data and return a number...
    i.e : when it is 0 in both columns, to return a 0.... then 1 , 0 to return a 1... etc.

    I have tried this;

    =IF((AND(D2=0, E2=0),0), IF(AND(D2=0, E2=1), 1), IF(AND(D2=1, E2=0), 2), 3)

    Any assistance would be greatly appreciated.

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

      =IF(AND(D2=0,E2=0),0,IF(AND(D2=0,E2=1),1,IF(AND(D2=1,E2=0),2,3)))

      1. Thank you so much for your speedy response. This resolved the issue.
        So close, yet so far. Bracket, everybody's best friend and worst enemy.

  18. Range Commission %
    > 800 16.0%
    > 600 to 400 to 299 to < 399 22.0%
    < 299 23.0%

    I have a large set of values I need to calculate the commission. How do I apply the formula in excel to calculate the commission value? Please advise.

  19. Hi, I'm trying to create a formula to show the following:

    If residency column is "Res" and "Off" = 32068
    If residency column is "Res" and "On" = 31846
    If residency column is "Nonres" and "Off"= 61564
    If residency column is "Nonres" and "On" = 61342

    Thanks!

    1. Hi!
      Please re-check the article above since it covers your case. I can't offer you a formula because I don't understand how you would write two values in one column (for example, "Res" and "Off")

  20. Hi
    I have multiple conditions to fulfill as described below, I am trying to use IF formula but it is not working. Please suggest.

    If a customer selects qty between 0-10 then no discount of total price.
    If a customer selects qty between 10-20 then 5% discount of total price.
    If a customer selects qty between 20-30 then 10% discount of total price.
    If a customer selects qty >30 then 15% discount of total price.

    Thank you

  21. I have problem, I have to values for L21 & L23 and final outcome is CA..

    CA= L23*3+L21*2 if value of L23 is greater than 0
    Like - L23 = 3 L21 = 2, then CA = 3*3+2*2=13

    and if L23 =0 and L21 =3, then CA=0

    Pls suggest farmula

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

      =IF(L23>0,L23*3+L21*2,IF(L21=3,0,""))

      You can learn more about nested IF statements in Excel in this article on our blog.

      1. I'm trying a formula where data exixts in one sheet, i need answer in one sheet with below condition, Can you please provide the formula for this

        Sheet 1: Answer & Sheet 2 : Existed Data

        I'm inputting formula in Answer sheet with below conditions

        If G column Data matches with A Column Data of "Existed Data and
        If J Column Data matches with B Coumn Data of "Existed Data and
        If Xcolumn Data matches with C columnd Data of : "Existed Data then
        answer to be appear which is in D column of Existed Data.

          1. I'm getting an error at end as The formula is missing an Opening or Closing Parenthesis.

              1. can i select entire column instead of cell

                =if(AND(Sheet1!A:A=Sheet2!A:A,Sheet1!B:B=Sheet2!B:B,Sheet1!C:C=Sheet2!C:C,=Sheet1!D:D,"")

              2. Hi!
                If I understood your task correctly, you want to compare cells in each row. Therefore, use a relative cell reference to each individual cell in the formula. After that, you can copy this formula down along the column.
                I also draw your attention to the fact that the use of column references in formulas significantly slows down the speed of Excel calculations.

  22. In a drop down tab there are 4 names, I want Excel to read selected name and some of the data can be written that person sheet in real time or command to copy

    I am totally beginner love to learn, I appreciate you had well explained the use of IF AND OR
    Thank you
    Hameed

  23. Hey! I want excel to perform different calculation depending on the outcome of a fraction, ive tried the following formula but cant get it working, how should i put it instead?

    =IF(SUM(I2/Q2)0.30.50.7, "R2*0.5", "R2*0.75", "R2*0.9", "R2*1.0"))

    1. Hi!
      I can't guess what conditions you want to check in your formula. Please describe them. However, all the necessary information to write the formula is in the article above.

  24. I have a problem where scoringh is made based on separate conditions being met and i am stumped.

    Example: If name = Type 1 and Condition = Y, score 2, otherwise score 1.

    The formula below doesn't seem to work properly.

    =IF(AND(Y2="Y"),IF(AND(P2="Type 1"),2,1))

    Additionally, If neither is it possible to have 0, rather than FALSE?

    If anyone is able to answer this for me, i'd be extremely grateful :)

      1. Thanks for your help, that's great!

  25. hi need this code to correct please help

    i have date on M17 given and the are encode MM/DD/YYYY
    i want to auto fill the date if 1st quarter or 2nd and so on.....

    this is my sample code but the result is always 4th Quarter need help to correct it thank you

    =IF(M17>=10/1/2022,"4th Quarter",IF(M17>=7/1/2022,"3rd Quarter",IF(M17>=4/1/2022,"2nd Quarter",IF(M17>=1/1/2022,"1st Quarter"))))

    2. i have date on M17 given and they are encoded on this format MM/DD/YYYY dateshort

  26. I will use this ( if(vlookup(a2, sheet1! A:ap, 42,0) ="TN", A2, "") fourmal this condition only sow TN state,
    Now I need TN & KA State which fourmal use plz help????

  27. Hello,
    I am trying to make eGFR calculator. It needs to differ between men and women and different serum concentrations of creatinine (SCr). Formula reads as follows:
    142 x (Scr/A)^B x 0.9938^age x (1.012 if female), where A and B are the following:

    Female Male

    SCr ≤0.7, A = 0.7 B = -0.241 SCr ≤0.9 A = 0.9 B = -0.302

    SCr >0.7 A = 0.7 B = -1.2 SCr >0.9 A = 0.9 B = -1.2

    So what I need is to Excel calculate/show value of B in one cell when specific conditions are met. For example patient is female and SCr is 0,55 (it is <0,7) B should be B=-0,241 and etc.
    Is it possible to test combinations of two variables (sex and SCr) to calculate three possible outcomes of B (-0,241, for women, -0,302 for men, and -1,2 in cases when SCr is greater than 0,7 for women, 0,9 for men, respectively)?

    Looking forward to your answer

    Edit of previous comment due to error.

  28. I please need help doing a formula for the following, which should just be one formula :Combine these into 1 formula
    if cell A is empty , it must use cell B info , if cell B is empty, it must use cell A info , If both cell A and B has info in it then it must use Cell B info, If no info then it must show a clear cell and not a 0.

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

      =IF(AND(ISBLANK(A1),ISBLANK(B1)),"", IF(ISBLANK(B1),A1,B1))

      To not show 0 in an empty cell, use custom number format.

  29. Hi there, I am looking for a formula which will return the following result:

    If text in cell A1 matches the text in cell range A1:A100, then show text from cell B1, assuming A1 is Name and B1 is Surname, please help.

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. Your terms and conditions are incorrect. The text in cell A1 will always match the text in range A1:A100 in cell A1.
      If you want to find the corresponding surname by name, then use the VLOOKUP function. For example:

      =VLOOKUP("Name",A1:B100,2,0)

  30. Hi

    =IF(OR(AND(L37="Yes"),OR(M36="Yes",N36="Yes",M37="Yes",N37="Yes")),"Yes","No")

    Effectively, the formula should be saying if L37 is Yes, and M37 or N37 is Yes, then give Yes, or if L37 is No, and M36 or N36 is Yes, then give Yes. Everything else, give No.

    the above formula is coming back as a Yes where it should say No.

    are there any other formalas i can use?

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

      =IF(AND(L37="Yes",OR(M37="Yes",N37="Yes")),"Yes", IF(AND(L37="No",OR(M36="Yes",N36="Yes")), "Yes","No"))

      Please re-check the article above since it covers your task.

  31. I am trying to get a formula for my budget.

    I need to calculate Management fees that are calculated at 2% of revenue. There is a minimum charge of $20,000.00 meaning that the least I will be invoiced for management fees is $20,000.00.
    So my formula needs to read that if 2% of my revenue is less then $20 000 then the answer is $20 000. If 2% of my revenue is greater than $20 000 then the answer should be 2% of that figure.

  32. Hi,

    Can You help me in this below Case

    =IF(F17=0, "F43*1", IF(F17=0.5, "F43*0.5", IF(F17=1, "F43*0")))

    Here In "F43*1" - Is also the formula

    Explanation,

    If F17 = 0, then F43 should multiplies with 1 (F43*1),

    Similarly, As follows

    If F17 = 0.5, then F43 should multiplies with 0.5 (F43*0.5),

    If F17 = 1, then F43 should multiplies with 0 (F43*0),

    *F43 is Sum of (F18:F42)

    1. Hi!
      In the IF function, as in other functions, quotes should only be used with text values.
      Please try the following formula:

      =IF(F17=0, F43*1, IF(F17=0.5, F43*0.5, IF(F17=1, F43*0, "")))

  33. Hi can you help me, the other formula is not working

    =IF(G1110,G11*24.17, IF(G11>20, G11*26.39, IF(G11>30, G11*28.92, IF(G11>40, G11*32.15)))))

    1. Hello!
      I can't test your formula because I don't know what result you want. But your formula was written incorrectly and contained an extra bracket.

      =IF(G1110,G11*24.17,IF(G11 > 20,G11*26.39,IF(G11 > 30,G11*28.92,IF(G11 > 40,G11*32.15))))

      1. here's the water rate per cu.m.

        0-10 CU.M = 224.02 (MINIMUM RATE)
        11-20 CU.M = 24.17 PER CU.M
        21-30 CU.M = 26.39 PER CU.M
        31-40 CU.M = 28.92 PER CU.M
        OVER 40 CU.M = 32.15 PER CU.M

        WHAT FORMULA SHOULD I USE, THAT ALL THE CONDITIONS WILL WORK.

        THANKS IN ADVANCE

  34. Hi, if in one cell (C25) I have 3 possible options (1, X, 2) then how can I get the values if 1=3, if X=1 and if 2=0? I typed the following formula, but with no luck

    =IF(C25=1;3,IF(C25=x;1,IF(C25=2;0)))

    1. Hello!
      Don't forget that character values must be enclosed in quotes.

      =IF(C25=1,3,IF(C25=2,0,IF(C25="X",1,"")))

  35. Here is my issue:
    =IF(AND(I4="Residential",J4="Self-Generated"),8%,6%)
    which I can get to calculate perfectly but I need it to have 4 conditions
    =IF(AND(I4="Residential",J4="Self-Generated"),8%,6%, AND (I4="Commercial",J4="Self-Generated"),10%,8%)
    which I can not get to work.

    If Residential & Self-Generated 8%
    If Residential & Company Generated 6%
    If Commercial & Self-Generated 10%
    If Commerical & Company Generated 8%

    Any help would be appreciated.

    1. Hello!
      Carefully read the recommendations in the article above. The formula might be something like this:

      =IF(AND(I4="Residential",J4="Self-Generated"),8%, IF(AND(I4="Residential",J4="Company Generated"),6%, IF(AND(I4="Commercial",J4="Self-Generated"),10%, IF(AND(I4="Commercial",J4="Company Generated"),8%,""))))

  36. I have the following COUNTIFS formula:

    =COUNTIFS($D$2:$D$494,A15,$C$2:$C$494,"A1",$N$2:$N$494,"<=15%")

    which works fine. However when I try to replace "15%" with a reference to a standalone cell containing a drop down with varying percentages, the formula returns an answer of "0".

    I have played with the formula for a few days and have to admit I am stumped. There is probably a simple solution but it escapes me.

    Any help would be greatly appreciated.

    1. Hello!
      Please check out this article to learn how to use cell references in COUNTIFS formulas.

      =COUNTIFS($D$2:$D$494,A15,$C$2:$C$494,”A1″,$N$2:$N$494,"<=" & A1")

  37. Hi

    I am trying to create an IF function where the following is applicable:
    Cell C4 has 3 options, "Yes", "N/A" and "No"
    Cell C5 has 3 options, "Yes", "N/A" and "No"
    Cell C6 has 3 options, "Yes", "N/A" and "No"

    Cell C7 contains the IF statement whereby if Cell C4 is Yes or N/A, Cell C5 is Yes or N/A and Cell C6 is Yes or N/A the result should be "Positive". Any "No" in Cell C4, C5 and C6 should be "Negative"

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

      =IF(OR(C4="No", C5="No", C6="No"),"Negative","Positive")

      1. I have a similar question to the original and I have the following formula in my spreadsheet -

        =IF(OR(H10="n",H11="n",H12="n"),"n", "y")

        My question is - how can I make the formula so that when all 3 cells are blank (haven't been tested yet) that the cell with the formula is blank? As the formula is now, that cell has a value of "y" and I haven't scored/populated the other cells yet.

        If I take out the "y" and just leave "" - I won't get a return of "y" when all 3 cells are "y" - it will just be blank.

          1. Hi - Thank you for answering my question so quickly. I believe I figured out a solution that took care of everything that I needed in the formula, (not just fiuring out how to leave the fomula cell blank - as I originally asked for your help with).

            I used -
            =IF(OR(H10:H13="n"),"n",IF(AND(H10:H13="x"),"x",IF(OR(H10:H13="y","x"),"y","")))

            That way my formula cell will be able to populate for any of the 3 possible answers - y, n and x (n/a) and remain blank until the other cells are populate.

            Thanks again, I really appreciate it! :)

  38. After IF, I need to use multiple AND and OR conditions and give multiple values for true and false accordingly, how do I do that

    1. Hi!
      All the necessary information is in the article above. If you give me more details, I’ll try to be more specific.

  39. Column A has a number and I need column B to be equal to column A, unless it’s it less than 2 I want it to be equal to 2.

  40. I am wracking my brain trying to sort my conundrum.

    I have 3 values Weekly(C5), 4 weekly(D5) and Monthly(E5) (which can all be inputted manually)

    I am trying to write the correct IF statement that allows me to create a yearly figure from only one of these values.

    e.g. if a user enters a figure in C5 the output would be C5*12, if users enter a figure D5 then the output would be D5*13 and finally, if the user enters a figure E5 then the output is E5*12.

    My problem is that I am only looking to output one of these figures to F5 as a yearly.

    I would appreciate some advice as I have looked at nested IFs and tried to use the conditions around C5=0, c5>=0. I have figured this out for using only 2 potential values but when I add in the 3rd I can't seem to figure out how to include/combine the other cells' values e.g. E=0, E5>=0 to give me the option of only one of the calculations working to give me a yearly.

    would appreciate any advice around this. TIA

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

      =IF(C5>0,C5*12,IF(D5>0,D5*12,IF(E5>0,E5*12,"")))

      I hope this will help

  41. HI,

    how can I combine the following:

    =IF((L2-M2)0,(L2-M2),0)

    Thank you for help.

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

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

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

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

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

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

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

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

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

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