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

  1. I was wondering if you could provide assistance with a formula I am trying to figure out.
    I need to look at a range of cells and determine if any of them have been the text "Ready" in them. IF they have the word Ready in them, then I need a different field to have a date in it. Is there a formula that can assist with this?

    1. Hi Jayne,

      You can use a formula similar to this:

      =IF(A1="Ready", TOADY())

      The formula will insert the current date if A1 contains "Ready". And you can replace TODAY() with any other date that you need.

  2. Hello,
    Which formula can I use if I want to return a cell value using a If clause. I am looking for something like " IF A1=OT Print value from B1.

    1. Hello Vinay,

      Here you go: =IF(A1="OT", B1, "")

  3. I need column A to reflect what is in column B. If B1=816114 I need A to say "SMC Main". If B=816164 I need A to say "CBRE". If B=811739 I need A to say "Ballard". If B says ANY other number, I need A to be blank (NOT say false). I am having trouble with that last part.

    1. Hi Brit,

      Try the following nested IF's:

      =IF(B1=816114, "SMC Main", IF(B1=816164, "CBRE", IF(B1=811739, "Ballard", "")))

      1. Thank you so much! worked great!

  4. Hi Svetlana,

    I am trying to obtain the most recent date from four different cells and want the data to return first if the most recent date is in cell AM4, second if the most recent date is in cell BH4, third if the most recent date is in cell CC4 and Fourth if the most recent date is in cell CX4. I am currently using the below formula which doesn't work if the most recent date is in cell CX4 and should return Fourth. Can you assist please?

    =IF(AND(AM4>BH4,AM4>CC4),"FIRST",IF(AND(BH4>AM4,BH4>CC4),"SECOND",IF(AND(CC4>AM4,CC4>BH4),"THIRD",IF(AND(CX4>AM4,CX4>BH4>CC4),"FOURTH",""))))

    1. I have now done this. Thanks

      =IF(AND(AM3>BH3,AM3>CC3,AM3>CX3),"FIRST",IF(AND(BH3>AM3,BH3>CC3,BH3>CX3),"SECOND",IF(AND(CC3>AM3,CC3>BH3,CC3>CX3),"THIRD",IF(AND(CX3>AM3,CX3>BH3,CX3>CC3),"FOURTH",""))))

  5. Very helpful article, thanks.

    I have a question though...

    I want a formula to return one of, say, 3 results based on the contents of a (source) cell on the same row but only if one specific string is present in that source cell and the result is defined by another specific string within the source cell.

    EG the source cell has "3m long Rubber cable" in it. The cables can only be 3m, 5m or 10m long but they can also be 'PVC' and 'High Temperature' types. So, in the example above, the formaula in the 'result' cell should return '3' because the word 'Rubber' is present in the source cell, and 3m is the length of the cable.

    I had been trying to use the 'IF(ISNUMBER(FIND("abc",[source cell reference])), [result],) formula which will work with half of my problem but how to combine it with more to pick not only 2 strings but also multiple alternatives is beyond me.

    Hope that's clear. Regards, Martin Winlow.

  6. Hello I am working on a spreadsheet and need help with a formula.

    If today's date is between the dates in C1 (8/31) and E1(9/7) I want it to display the data in cell D3, if todays date is between the dates in E1 (9/7) and G1 (9/14) then display the data in Cell (F3). Can someone please help me?

    Thank you in advance

  7. Please help me come up with a formulae to do the both the following in one go

    there are 6 Subjects , I need to give grades for average marks above 90,80,70,60,50,35 and below 35 A,B,C,D,E, Pass and Fail respectively. Also if single subjects are lesser than 35 then it should be fail

    please let me know the if function for the above

  8. Hi,
    Below condition is not working
    IF(OR(A1=C1,B1*D1),IF(A1=C2,B2*D2),IF(A1=C3,B3*D3))

    Column A Column B Column C Column D
    B81234 16 B91456 $8,995.00
    B81345 19 B81234 $4,887.50
    B91456 27 B81345 $5,391.00

    can anyone help me
    Manish

    1. Hi Manish,

      I believe the correct syntax is as follows:

      =IF(A1=C1, B1*D1, IF(A1=C2, B2*D2, IF(A1=C3, B3*D3, "")))

  9. Good Morning, I'm trying to achieve the following:

    IF column "FIRST NAME" OR column "SECOND NAME" contains "Ltd" OR "Plc" THEN Message "company details must be entered in the "TITLE" column".

    Can you help please?

  10. Hiya,

    Please help me come up with a formulae to do the both the following in one go

    PQ63483C to become PQ63483
    CRD63483C to become CRD63483

    In essence just to copy but remove the final C off of any data put in the A Column

    I keep experimenting and I can't seem to work out a conditional formulae that works for both

  11. hi, I'm trying to write a formula that returns a traffic light system with the following

    cell A2=TODAY()
    cell B2=Due date (01/11/15)
    cell C2=date project to be started (01/09/15)
    cell d2=Completed

    i want the traffic light return green if due date is more than 2 months away
    i want the traffic light to return yellow if due date is within the next two months
    i want the traffic light to return red if due date is greater than today
    i want the traffic light to return green if completed

    can anyone help me with this please
    thanks
    Andy

  12. how will i do the "if" function for the ledger account, for example CASH

    CASH
    Debit Credit

    8000 5000
    -----------------------------
    3000 Balance

    and what if the "credit" balance exceeds the "debit" balance
    how will i use the "if" function to make the credit balance be written under the credit column.

    Thanks

    1. Hi Josselle,

      Let me check if I understand the task correctly.

      If Debit (A2) is greater than or equal to Credit (B2), we calculate the difference (Debit-Credit). If Credit is greater than Debit, we return the Credit number.

      If so, you can use the following formula:
      =IF(A2>=B2, A2-B2, B2)

      If you are looking for something different, please clarify.

  13. APOLOGIES ... THIS IS THE FULL FORMULA I HAVE:
    =IF(F2>=11,"Late",IF(F2>=-10,"On Time",IF(F2<=-11,"Early")))
    I want to include in the middle part/argument, that if C2 (which is a particular date) to say "CR ON TIME"

    1. Hi Robin,

      If my understanding is correct, you can add one more IF to the formula, like this:

      =IF(C2<>"", "CR ON TIME", IF(F2>=11, "Late", IF(F2>=-10, "On Time", IF(F2<=-11, "Early"))))

  14. I need help!!!
    I have this following formula:
    =IF(F2>=11,"Late",IF(F2>=-10,"On Time",IF(F2=10 to say on time or if c2 (has a date) to say "CR On Time" ...does this make sense???!

  15. Hi Iira,

    You can add another IF function to the formula, like this:
    =IF((F4+F5+F6+F7)='Fee summary'!G5,'Fee summary'!G5, IF((F4+F5+F6+F7)<'Fee summary'!G5, "ERROR > "&'Fee summary'!G5, "")) Please note, the formula returns an empty string if your sum is greater than in 'Fee summary'!G5.

    1. Thank you so much Svetlana for your advice.
      I have changed it slightly to add the opposite text.

      =IF((F4+F5+F6+F7)='Fee Summary'!G5,'Fee Summary'!G5, IF((F4+F5+F6+F7)<'Fee Summary'!G5, "ERROR £"&'Fee Summary'!G5))

      I'd like to ask you one more question. If I don't have any values in F4, F5,F6,F7, I'd like also to write the value of 'Fee Summary'!G5. Where do I need to add that condition in the formula? It's a kind of AND/OR I guess...
      At the moment it says:
      =IF((F4+F5+F6+F7)='Fee Summary'!G5,'Fee Summary'!G5
      It would be another condition into that one...Do you think it's possible?
      Something like (but added to rest of the formula):
      =IF((F4+F5+F6+F7)=0,'Fee Summary'!G5

      Thank you very much for all your help.

      1. Yep, the OR statement is what you need:

        =IF(OR(F4+F5+F6+F7)='Fee Summary'!G5, (F4+F5+F6+F7)=0),'Fee Summary'!G5, IF((F4+F5+F6+F7)<'Fee Summary'!G5, "ERROR £"&'Fee Summary'!G5))

  16. Hi,
    I need help with a formula that related to a cell in a different sheet.
    I did this formula:
    =IF((F4+F5+F6+F7)='Fee Summary'!G5,'Fee Summary'!G5,"ERROR")
    However, I know need to return a value:
    If my sum is < than the value in 'Fee Summary'!G5, I need to write "ERROR"ERROR> (and the value in 'Fee Summary'!G5) and if it's equal, just return the value.

    I would appreciate any help to sort it out. Thanks!

  17. Hi Svetlana
    I would like to start by thanking you for such a great website. Your examples and explanations I find are very clear and easy to follow. I was wondering if you could help me with a formula. I used a copy of one above to write the following:
    =IF(OR(AND(D2=11, L2>10), AND(D2=12,O2>10), AND(D2=13,R2>10)),"OK","NO") and this works fine.

    But I want to now return extra results based on the second condition. For instance if D2=11 and L2>10 "Yes" or if L2 is between 6-8 "OK" or L2<8 "NO" and I want it to repeat for D2=12 and D2=13?

    I hope this makes sense to you. Thank you for any help you could offer. I would really appreciate it.

  18. Hi Svetlana,

    i need help on the below.i am working on a formula for calculating diesel use.

    CELL A. Indoor or Outdoor
    Cell B. 20
    Cell C. 31
    i want the following conditions to apply.

    if CELL A is either indoor or outdoor, & CELL C is > Cell B, then return value "high", if if CELL A is outdoor, & CELL B is > Cell C with a value greater than 3 , then return value "Low", if however CELL A is Indoor and 50% greater than CELL C, then return value "too high" otherwise pass

    i hope this is clear enough

    1. Hi Chidike,

      You can use a nested IF formula similar to this:

      = IF(AND(A2="outdoor", C2>B2, B2>3), "low", IF(AND(A2="indoor", (B2-C2)/C2>0.5), "too high", IF(AND(OR(A2="outdoor", A2="indoor"), C2>B2), "high", "pass")))

  19. suppose i have 4 columns like A B C D, and i want to use IF foumula. A column contains xxx & others & B column yyyy & other. then if a columns contains xxx & b columns contains yyy then how should i use if formula

    1. Hi Pirsabm,

      You can include the AND statement in the logical test, like this:
      =IF(AND(A1="xxx", B1="yyy"), value_if_true, value_if_false)

  20. Hi, i just wanted to know that if we had 7 columns containing different numbers and if any of 2 columns had 0 value then should be considered dis qualified.

    1. Hi Hadi,

      You can use a formula similar to this:
      =IF(COUNTIF($A1:$G1, 0)>1, "dis", "")

      1. o thanks svetlana, it was so easy and very very helpful. thanks alot....

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

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

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

  24. 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%, "")

  25. 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","")))))

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

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

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

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

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

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

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

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

  34. Gostei! Foi pra mim muito util!

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

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

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

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

  39. 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)]

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

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

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

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

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

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

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

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

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

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

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

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