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

  1. Hello, I am trying to get column E to be the result of column C +D. IF columns C+D=2, then "0" IF columns C+D>2,"1" but I am not sure how to do this so the entire column is calculated (row by row). Please tell me I do not have to go through individually with the command (I have over 2000 rows). Thank you!

    1. Hello ELISE,

      You can enter the following formula in cell E1 and then copy it down to other cells in the column by selecting E1 and dragging down the fill handle:
      =IF(C1+D1=2, 0, IF(C1+D1>2, 1, ""))

      Please note that if C1+D1<2, the formula will return an empty string (blank cell).

  2. Hi Svetlana,

    0-15 days - 0%
    15-30 days - 5%
    31-50 days - 10%
    >50 days - 15%

    I would like to receive a formula for said %. Can you pls help me.

    Regards,
    Madhu Babu

  3. Good afternoon Svetlana,
    I really need some help on this, I am working on a time sheet and where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the out to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

  4. Hey,

    I have searched everywhere to find the correct conditions for my IF function formula. Please help!

    If the cell value is over $50,000 but not over $75,000, add $7,500 plus 25% of the amount over $50,000. I need the range to be over $50,000 but not over $75,000.

    For Example:
    If the cell value is 60,000, I need the formula to add $7,500 plus (excess amount over $50,000)*25% [7500+(60000-50000)]

    1. Hi!

      If my understanding of the task is correct, the following formula should work a treat:
      =IF(AND(A1>50000, A1<75000), 7500+(A1-50000)*25%, "")

  5. How can I get this type of formula to work? I am trying to get a value if criteria are met in two cells that comprise of multiple drop down list values.

    =OR((((IF(AND(C2=1,D2="Towers"),"Nito",""),IF(AND(C2=1,D2="A-Pick"),"Nito",""),IF(AND(C2=2,D2="Towers"),"Angeline","")))))

  6. Hi Svetlana,
    What do the quotation marks in the formula below stand for?

    =IF(L23="","",(L23-$L$31)^2)

    Thank you.
    Steve

    1. Hi Stephen,

      "" stands for an empty string, i.e. a blank cell.

      So, the formula reads as follows: if L23 is empty (or contains an empty string returned by some other formula), then return nothing (blank cell), otherwise return the result of the calculation.

      1. IF Column A anything equal to Column B then select the value from column C corresponding to Column B.
        I have two different dates in Column A and Column B and want to match them and after matching select the value from Column C corresponding to Column B

        1. Hi!
          Unfortunately, I can't understand what you want to do. Explain what it means to "compare two different dates" and "select the value from Column C corresponding to Column B".

  7. Hi,
    Is someone able to help me please, i'm trying to work out how to get different values based on day parameters, for example starting with 30 June, day 1 to 7 is charged at $75 a day, then day 8 to 14 is charged at $105, then any days from day 15 is then charged at $135, so i'm trying to based on the number of days charged, bring across the right day charge based on the day parameter.

    All this is from one line in excel.

    Hope you can help.

    Thanks
    Chantelle

  8. Thank you Svetlana

    I have another question. I have a vlookup that is wrapped around an IFERROR function. So when there is an error the cell returns blank. Now I need to have conditional formatting on that blank cell. What is the rule with reference to that cell. Using the cell value is equal to blank or N/A doesn't seem to work. Can you provide some expertise?

    thanks again

    1. Hi Michelle,

      You can create a rule with the formula like =$B2="" where B2 is the top-most cell with your IFERROR/VLOOKUP formula.

  9. hi is someone able to help with the following:

    I have a date format in one cell (CQ)that appears like this 2015/07/24 and I would like to link an IF formula to that date cell. The intention is if the CQ date cell has a date in it (numeric) return the date in tact. And if the same CQ cell contains nothing (blank) return blank "". I am using the following formula but it isn't working:

    =IF(CQ7="numeric",CQ7,"")

    Unfortunately it doesn't like number, date or numeric. What can I put in the spot of "numeric" that will make this formula work?

    thank you in advance

    1. Hi Michelle,

      Excel has a special function, ISNUMBER, to identify numeric values. So, you can write the formula as follows:
      =IF(ISNUMBER(CQ7), CQ7, "")

      Please note that it returns a serial number representing the date and to force it to appear as a date, you need to apply the Date format to the cell with the above formula.

  10. I am trying to find a relatively simple (I hope) formula for a time sheet.

    Column C is the Start Time

    Column E is the End Time.

    Colmun F would be the total hours worked.

    I have worked out =sum(E3-C3)/100-0.3

    What I need is to equate "OFF" and "LV" to 0 in F3.

    Thanks in advance!

    1. Hi Nicole,

      Sorry, I am not sure I understand the task. Do you enter that formula in F3 and want it to display "OFF" or "LV" when the formula returns 0? Please clarify.

  11. Hi I am wondering what the underlying is in the following formula:
    =IFERROR(VLOOKUP(B6,'Asset Mix'!W:GR,178,FALSE),"")

    Without the "" at the end of the formula, it would return #N/A. I am trying to link a formula in another cell to the blank cell in the formula above, but it won't accept the "" as a blank - what is really in this cell?

  12. Hi Svetlana

    Could you please help me with the below.

    I would like to have one cell with following two formulas:

    =D43*H20

    But also:

    =IF(E43="","","D43*H20")

    So if E43 is blank then the cell is blank and if it is not blank then it contains the formula D43*H20

    Is this possible?

    Thanks so much

    Luke

    1. Hi Samuel,

      You were almost there :) Just remove the quotation marks enclosing the formula in the last argument:

      =IF(E43="","", D43*H20)

  13. Hi
    I am stuck in a situtation where I have multiple excel sheet and I am trying to generate a summery sheet which gives me quick detail here but not able to move ahead. Let me explain what I am looking for: I have various locations where some sales activities will keep on happening everymonth. Now I want to know how many activities happen in a period of 1 month in a particular area. The complexity is that in other sheet the same area is mentioned several times as dates may differ.

    Now I want that in the summary sheet, I should get that area to be mentioned once and no. of activities happened in that area in the entire month.

    Please help!

  14. Gostei! Foi pra mim muito util!

  15. I have this problem;
    When the value from a cell in column A is different to the value in the cell below, use column C otherwise use column B. Now if the value in column C is greater then 1000000 default the value to 1000000 and if it’s less then 0 default it to 0.

    My Solution:
    =IF(A1A2, IF(C1>100000, 100000, 0),B1)

    Please help me get correct results

  16. =IF(AND(I20="Non-US", E20>E13), "XS1261825977", "N/A"), IF(AND(I20="Accredited Investor", E20>E13), "XS1261826512", "N/A"), IF(AND(I20="QIB", E20>E13), "XS1261826512", "N/A"), IF(AND(I20="Non-eligible", E20>E13), "N/A", "N/A")

    E20>E13 is simply checking that a numerical amount is higher than another.

    Hello.

    is there aything immediately obvious with the above formula?

    is it OK to nest IF(AND) like this?

    I am getting the #VALUE! error.

  17. Hi team, I have been reading through the article and all comments attempting to solve for a broken formula, so I am hoping I can call upon your collective brain cells to solve for this.

    I am trying to determine if a document was created before or after a certain date, pulling from an array of data. My formula (and I don't know what I'm doing wrong here), looks like this:

    =IF((AND(ENCOUNTERS!D:D,"="&B5,ENCOUNTERS!G:G,">"&C5)),"YES","NO")

    I'm getting a #VALUE error within the cell.

    Data within B5 is an ID number
    Data within Encounters!D:D is a list of ID numbers
    Data within C5 is the specific date in question
    Data within Encounters!G:G is a date corresponding to the ID number

    I think my edrror has something to do with the fact that I'm working with dates, but I am uncertain. Any feedback would be dearly appreciated.

  18. When I concatenate 2 date columns and display, I use the formula
    =TEXT([A1],"mm/dd")&" - "&TEXT([B1],"mm/dd")

    I need to add an additional condition , If either A1 or B1 or both are empty , nothing should be concatenated and displayed.

    =IF(OR(AND(A1="", B1=""), AND(A1="")), ,( TEXT([A1],"mm/dd")&" - "&TEXT([B1],"mm/dd")))

    I wonder is this will work. Any help is appreciated.

  19. Hey,

    I have searched everywhere to find the correct conditions for my IF function formula. Please help!

    If the cell value is over $50,000 but not over $75,000, add $7,500 plus 25% of the amount over $50,000. I need the range to be over $50,000 but not over $75,000.

    For Example:
    If the cell value is 60,000, I need the formula to add $7,500 plus (excess amount over $50,000)*25% [7500+(60000-50000)]

  20. Hey

    "In cell J9, use a function to display the name, school and symbol of the top student as such Smith, A from Cape High School scored A."

    for this question what function can I use to consolidate name, school and grade of the top student?

  21. IF
    functions
    which
    returns
    NO
    NEED
    or
    SEND
    NOW
    or
    CHECK
    IT
    IN
    TWO
    WEEKS

  22. A B (Values) C(Answer)
    MISJ 5000 5000
    DIV 500 500
    MISJ 1500 1500
    JV 1000 0

    Please help me to develop formula for above calculation
    I need " MISJ"and "DIV" values in C column

    1. Hi Nandana,

      Here's the formula for C1:

      =IF(OR(A1="misj", A1="div"), B1, 0)

  23. I have sets of data, for example like this
    Sam Bella Sara Adam
    Modal $1,000 $2,000 $3,000 $1,000
    Work Order Cost
    Sam, Adam $100
    Bella, Sara, Adam $200

    I need to divide the value of 'cost' to the respective individu. For example, 'cost' $100. I want this value divide evenly to Sam and Adam based on their respective modal value . So the calculation supposedly, ($100x$1000)/($1000+$1000) = $50. Same goes with Adam since their modal value is same.

    So I want a formula which can capture the names and return the value needed in the calculation.

    Please assist me to solve this. Thanks.

  24. if A1=blanck (no value) then B2 (where the curser is)should be blank
    if A1 is "not like" "ES/*" then B2 NM
    Pl let me know the formula

  25. In excel file I have 5 coloums with different name.

    In another excel file I have 100 rows with 2 coloums.

    In one coloumn there is amount and another coloumn there is names which is there in 1st excel file

    please suggest a formula through which I can sum the amount from 2nd excel sheet to 1st excel sheet according to the name.

  26. This was incredibly helpful. Excellent article. Thank you!

  27. Dear Svetlana,

    I am also trying to learn some of the insides. Please help with the following: let's say I have 10 products with a cost of 1,2,3,4,5..10Eur. In order to find a sale price I want to put them in categories: from 1-4,5-7,8-10. Each category has a different percentage added: 1-4: 200%; 5-7: 150%; and 8-10: 100%.
    How can I link a formula to automatically multiply the purchase cost with the right percentage?
    Thank you!

  28. Hi,
    I am trying to write formula for the following condition in my work sheet but I am unable to write can I get help?

    condition : first condition if(c2=1) and count(P2:R2)=0,then "OD", "OT" ) or if(c2=2) and count (M2:R2)=0, then"OD", "OT" )

  29. Hi,
    I'm looking for a formula that could be used like this:

    Year number : 1 - 30
    Tables x, y, z the number of purchases made in table x which is in euros, the number of purchases made in table y in francs, and the number of purchases made in dollars.

    If I chose year 6 it will give me a value in tables x. THEN IF I wish to chose year 19 it will give me another value in table x. How do I make this formula as I write in the number? Thanks!

  30. Hello,
    This formula works: =ROUND(IF($E5="MCP",$V5*Rates!B$3,0),2) and this works: =ROUND(IF($E5="CWA",$V5*Rates!B$4,0),2) but I want them be together in the same formaula...multiple conditions. For some reason, today, I am just drawing a blank and am having trouble combining the two.

    Note: Rates!B$3 = 3% and Rates!B$3 = 4% and $E5 is a dollar amount

    Thank you.

    1. Never mind. I got. :-)

  31. Hi there

    What formula do i use to get correct result for:-
    if Cell A2 contains "ABC" and B2 cell contains "100%" then i want C2 cell shows "OK" & if Cell A2 contains "DEF" and B2 cell contains ">=50" then i want C2 cell shows "OK" & if Cell A2 contains "XYZ" and B2 cell contains ">=35%" then i want C2 cell shows "OK" otherwise C2 shows "Pending"
    Like below:-
    Pocedure Result Process
    ABC 100% OK
    DEF 50% OK
    XYZ 35% OK
    ABC 90% Pending
    XYZ 35% OK
    DEF 40% Pending
    ABC 75% Pending
    XYZ 10% Pending

  32. Very informative Site: i am having a problem please solve it out, Thanks.

    I want Result in a certain cell: if Value in E3 is above 400000 but less than 750000, then (E3-400000) x 5% and add zero, but if value is greator than 750000 but less than 1400000, then (E3-750000) x 10%+17500, i am unable to figure it out,
    Please help

    I am using this formula. IF((AND(E3>=400000,E3=750000,E3<1400000),(E3-750000)*10%+17500),0)

  33. Hi,

    What formula can I use to assign if the particular time is of AM or PM shift? Here's the scenario:

    AM shift falls within 8:00 AM to 19:59 PM
    PM shift falls within 20:00 PM to 7:59 AM

    However, cell value is in date and time, while above are of time value only.

    Thanks!

  34. =IF(OR(ISNUMBER(SEARCH("Top 25%",E9)),ISNUMBER(SEARCH("Top 50%",E9))*ISNUMBER(SEARCH("Bottom 50%",E9))),"12","8")

    In the above formula I want "Bottom 50%" to be 4. if I am adding 4 after eight in formula. I get error .

    Please help

  35. Can someone assist me. I have the following criteria I have a range of cells from F4:F14 where some cells have text and the others have numbers.
    What I need to accomplish:

    Pass if cell value F4 is 0.1 or higher
    Pass if cell value F4 has any text
    Fail if cell value F4 is equal to 0
    then I need to display (Pass / Fail) in cell F15 which is the total of F4:F14

  36. Hello
    Could you please help me with the following formula:
    If a cell (date of booking) is blank then 0 or if the cell ( date of booking )> another cell (date of enquiry) then also 0 otherwise 1.
    Thank you

  37. Hi,

    I want to report a result =0.05 in one digit. Is it possible with 'IF' condition.

    Regards
    Kumar swamy

  38. if, 1)for 0 to 250000 ,tax is 0%
    2)for 250000 to 500000 tax is 10%
    3)for 500000 to 1000000 tax is20%
    for 1000000 to above tax 30%
    e.g.,if taxable income is 650000 then tax will be
    for 1) for 250000x0%=0
    2) for 250000x10%= 25000
    3) for 150000x20%=30000
    so, total tax = 55000
    what will be the formula

  39. Hi Svetlana,
    I hope you can help me I really need your assistance on this. I am working on a time sheet where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the output to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

  40. HI,

    I could also use some assistance with another formula. I would like to sum the values from FX:GE to get a total score, but if the total score = 16, I would like it say "-1" instead. Is this possible?

    Thanks!

    1. Hi Courtney,

      Here you go:
      =IF(SUM(FX1:GE100)=16,-1,SUM(FX1:GE100))

  41. Hi Svetlana,

    Thank you for all of your support on this amazing website! I would appreciate your help making one If/then statement for the following:

    If GI = 8, then "-1"
    If GF = 8, then "1"
    If GF + GG = 8, then "2"
    If GH + GI = 1 or 2, then "3"
    If GH + GI = 3 or 4, then "4"
    If GH + GI > 4, then "5"

    Thank you!

    1. Hi Courtney,

      Here's the formula as per your conditions:
      =IF(GI1=8,-1, IF(GF1=8,1, IF(GF1+GG1=8,2, IF(OR(GH1+GI1=1,GH1+GI1=2),3, IF(OR(GH1+GI1=3,GH1+GI1=4),4, IF(GH1+GI1>4,5,""))))))

      Please keep in mind that nested IF formulas elevate the first condition first and if it's met, other conditions are not tested. For example, if GI1=8, the formula will always return -1 regardless of the values in other cells.

  42. Hi, so I'm trying to compare two values but they are not integers. How can I do this?

    Condition: If A1 is "High" and B2 is "Low", C1 should be "Q1". This would be different if A1 is Low and B2 is High, it will print "Q2" and so on. IS this possible?

    1. Hi Ivan,

      Of course this is possible with the following formula:

      =IF(AND(A1="high",B2="low"),"Q1",IF(AND(A1="low",B2="high"),"Q2",""))

  43. Good afternoon,
    I am working on a time sheet where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the out to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

  44. Svetlana,

    Hi! I have a spreadsheet that I am using to calculate a certain ratio. A lot of times it will come back with a null value which is what I want. Formula is: =IFERROR(AR416/H416,"")

    But, there are times where the value comes to $0.00 which unfortunately gets pulled into my AVERAGEIFS formula and pulls down the average considerably.

    So I tried this formula to counteract that:
    =IF(AR419/H419>0,IFERROR(AR419/H419,""),"")

    But now I get the divide by zero error in a few cells: #DIV/0!.

    Is there a way to use an IF statement to return a null value for everything that isn't greater than 0?

    Thanks so much!
    Shawn

  45. Perfect! Thanks so much for your help Svetlana!

  46. Hi WENDY,

    Try this one:

    =IF(AND(R1126>10.1,S1126>2.1),"TF2", IF(R1126>=10.1,"TF1", IF(S1126>=6.1, 3, IF(S1126>=2.1, 2,IF(S1126<=2,1)))))

  47. Hi! I am trying to add one more equation to my IF function and am not sure what to do. Here is my equation so far: =IF(R1126>=10.1,"TF1",IF(S1126>=6.1,"3",IF(S1126>=2.1,"2",IF(S1126<=2,"1")))) What I also need it to do is come back with a posting of TF2 if R1126 is greater than 10.1 AND S1126 is greater that 2.1. I cannot figure out what I am doing wrong. Any suggestions?

  48. Hi

    Can you help me with why this function is giving invalid errors? My error starts at this statement:IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA"))

    and again at this statement:IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

    =IF(F43="",IF(A43>=TODAY(),("OTW"),IF(B43="",("CHECK ETA"),("ARRIVED")),IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA")),IF(D43="",IF(B43"",("AT PORT"),("OTW")),IF(E43="",IF(D43"",("INGATED"),IF(B43"",("AT THE PORT"),("OTW")),IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

  49. Hi Svetlana

    How ican convert the currency value to USD if the available currency is anything else say , in 1 row (A:A) I have AED, Pounds, Euro, RS and they should be converted to USD in another ROW (B).
    1 more point is for the convenience in Row B we can see what currency has hadded. Like A1: 100 B1: Eur and in C1 : I have to have A1 value in USD here. We have standard conversion value.usd to Eur is 1.09 and for AED to USD is 3.67. so n so.

    Regards
    Rajesh

  50. I have several false and true statements across a row. I want to make one formula that says if all statements are false, give me false. If one is true, give me true.

    1. Hi Sarah,

      You can use a formula similar to this:
      =IF(COUNTIF(A1:E1, TRUE)>0, TRUE, FALSE)

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