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

  1. Hello,

    =IF(V4>=27, "Excellent", IF(V4>=19, "Good", IF(V4>=9, "Satisfactory", "Poor "))))
    Can you help me regrading this formula because ist not working when try with Excel file , I have value (V4) in my file from 12 to 28,
    When I try in single mode formula working properly,

  2. Hi. I am making a simple formula for tests results. I have already a list for the percentage of the raw scores. All I wanna do is when I input the raw score (1st column) its equivalent percentage will automatically show in the next column. :)

  3. Hi,
    I am having trouble combining the following rules:
    1. If A:A ="Amy" AND B:B contains "Show", "Show"
    2. If A:A ="Amy" AND B:B contains "Dress", "Dress"
    3. If A:A does not equal "Amy", "other"

    Thanks

    1. Hi rue,

      If you want to return a value from column B if column A contains "Amy", "other" otherwise, use the following formula:
      =IF(A1="Amy", B1, "other")

      If you need a formula for 2 conditions, "amy" in A and "show" / "dress" in B, use the following one:

      =IF(AND(A1="amy",OR(B1="show", B1="dress")), B1,"other")

  4. Hello, new here. Please help me.

    Amount of Net Taxable Income Rate
    over but not over
    10,000.00 5%
    10,000.00 30,000.00 500+ 10% of the excess over 10,000
    30,000.00 70,000.00 2,500+15% of the excess over 30,000
    70,000.00 140,000.00 8,500 +20% of the exces over 70,000
    140,000.00 250,000.00 22,500+25 % of the excess over 140,000
    250,000.00 500,000.00 50,000 +30% of the excess over 250,000
    500,000.00 125,000 + 32% of the excess over 500,000

    based on above tax table i would like to come up with a formula to find the tax due of the following taxable income:

    1,070,018.83
    277,667.62
    400,066.46
    191,497.18
    877,016.35
    504,028.75
    116,979.02
    748,277.72

    Thank you in advance to those who can help.

  5. Hi Svetlana,
    I am unable to figure this out.
    I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding "1 In Production" cell.

    Many Thanks

  6. I have this formula
    If((AND(S1="CALIFORNIAN", T1=0.5, EXACT(UPPER(X1),X1), LEN(X1)>15)), TRUE, FALSE

    This formula works good if all characters are upper case. But I want this formula to also do this, if it is not all upper case then change length to >17. So if my text is Proper the text string can be longer.

    Thanks for your help

  7. I need a formula!!!
    can you please help me on this....

    I have 4 cells with dates
    If I enter date in first cell then value should be equal to 10%,
    If I enter date in first and Second cells then value should be equal to 30%,
    If I enter date in first and Second and third cells then value should be equal to 50%,
    If I enter date in first and Second and third and fourth cells then value should be equal to 100%.

    Pls help me

  8. Hi im trying that

    if input is >100000 and 200000 to < 300000 to be consider as 2 points

    like this N number of times.

    Means each lack to be consider as 1 point, how to make a formula in excel

  9. Need to create a formula that works for the data below. Please help.
    6.50 = Diamond

    Thanks in advance.

  10. Hi Svetlana,

    You have helped me before with Excel and I need your help again.

    I am developing a spreadsheet with details of research studies. I have a comments column which I want to fill in as "Closed" if the study end date in Column N has passed. At the same time for some studies I don't have study end date so unless the date is passed it is showing false whereas I want it to be blank if column N has no date or if teh study hasn't closed yet. Formula I am using is
    =IF(AND(N18"",N180),IF((TODAY()-N18)>0,"Closed",IF((TODAY()-N18)<0,"")))

    Many thanks for your help

    BW

    Jamil

  11. Hi,

    I need help with a formula

    my date condition 01 Oct 2015

    If date >= 01 Oct 2015, I need to meet A1 cell value >=70 to equal 70 & <=(10) to equal (10), otherwise A1

    If date <= 01 Oct 2015, my answer is A1

  12. Hi,

    I need help with a formula.

    I want to return the following values;

    If Cell Value is less than 40 = 10
    If Cell Value is between 40 & 49 = 6
    If Cell Value is between 50 & 59 = 4
    If Cell Value is between 60 & 64 = 2
    If Cell Value is greater than or equal to 65 = 1

    I've tried using the formula below but it only seems to return either the numbers 10 or 6.

    =IF(C2=40,"6",IF(C2>=50,"4",IF(C2>=60,"2",IF(C2>=65,"1")))))

    1. I have the same problem, how did you fix it?

    2. I figured out why the formula wasn't working, i've managed to sort it out.
      Thanks

  13. if any of two cells out of 3 cells are "false"(row wise)..then the third cell value should be in the "Result" column.

  14. I want help with a formula,

    I have 3 columns for audit, if all three columns says Yes then i want the finding to return as a smile.

    if any of the columns have a NO then the finding to return as Non Compliant..

    can you please help

  15. Hi Svetlana,

    Could you please help me to figure out the formula, which will find identical cells with identical ID, Instrument, Amount, but different Type and will return "hedge"

    ID Instrument Type Amount
    1212 XAUUSD Buy 30
    1234 CL Buy 1000
    1255 XAUUSD Buy 40
    1255 APPLE Buy 150
    1255 XAUUSD Buy 20
    1255 XAUUSD Sell 20

    Thank you ahead!

    1. apologies for the format appeared above.
      However, for the case above the last two rows should appear as "hedge"

  16. Hi,

    IF 1 to 749 should count as 1 and in case if its blank it should not calculate or it should show as 0)

    please help

  17. I guess non costumers are not welcome...

    1. customers*

  18. Hi Svetlana cheusheva.......
    how to use if condition in my ques......
    plz tell me....
    * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

  19. I have 3 columns, Lets say A1, B1, and C1. I need to make a concatenate function that:

    If A1="*" and B1=(random text) then C1 = B1
    If B1="" and A1=(random text) then C1 = A1
    If A1"*" and B1"" then =CONCATENATE(A1,".",B1)

    1. Hi Austin,

      Does A1"*" mean any text in A1? And most likely B1"" is supposed to be <>""(non-empty), right?

      Anyway, you can use a nested IF formula similar to the below one, just adjust the criteria according to your needs:

      =IF(AND(ISTEXT(A1), B1="text"), B1, IF(AND(A1="text", B1=""), A1, IF(AND(ISTEXT(A1), B1<>""), CONCATENATE(A1,".",B1), "")))

  20. =IF(I11=26,"10000",IF(I11=27,"15000",IF(I11=28,"20000")))-E8

    I11 its +1 and value is +5000
    is there shorter way to do this?

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

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

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

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

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

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

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

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

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

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

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

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

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

  34. 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???!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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