Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 20. Total comments: 4823

  1. How to formulate this into the function

    if cell A1 is multiplied by a number then put value of 1 in cell A2

    1. hello!
      How do you want to know that cell A1 is being multiplied by a number? Please describe this condition in detail.

  2. Hi.

    I'm trying to lookup if text matches to return a text string and if it doesn't match, to lookup in another sheet, but it only is seeming to check the first lookup, not the second.

    =IF(VLOOKUP(C2687,'Master Data'!A:A,1,FALSE)=C2687, "regular listed", IF(VLOOKUP(C2687,'Community Service - Master Data'!A:A,1,FALSE)=C2687, "community service", "No Master Data!"))

  3. Hello I'm looking to have the formula work: =ISTEXT(IF('Investment Center'!$B$6:B26=E46,'Investment Center'!$C$6:C26))

    All fields are text and I want to have the following logic. If anywhere within Investment Center'!$B$6:B26 equals to E46 then return value of 'Investment Center'!$C$6:C26. All entires are text.

    1. Hello!
      If I understand you correctly, you want to return not one value, but an array. Then try this formula

      =IF(SUM(--(B6:B26=$E$46))>0,C6:C26,"")

      Hope this is what you need.

  4. =IF(L444<3,"PASS","FAIL")=IF 0 =N/A

    HELP Please

  5. Hello.
    I'm trying to us an IF function to a cell that has a small formula F7 has this formula(=F1-F2) but it wont work. It will work if I type the correct number in there but if it is coming from the small formula it is coming up up as "False" Can you help?
    F7 has this formula (=F1-F2)
    Example: If F7...
    is greater than or equal to 0.5 then 2
    is less than or equal to -0.5 then -2
    is in the range of 0.4 to -0.4 then 1
    All in one formula?
    I hope that makes sense.

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

      =IF((F1-F2) >= 0.5,2,IF((F1-F2) <= -0.5,-2,IF(AND((F1-F2) >= -0.4,(F1-F2) <= 0.4),1,"")))

      I hope it’ll be helpful.

  6. Hi.

    I am trying to do a product costing spreadsheet. working with 3 different rates of VAT. i'm hoping to indicate which one of the rates applies to the product with a check box? and then using the IF function to give me 'cost per item' based on what rate is selected because the others don't apply.

    Hope this makes sense :)

    1. Hello!
      You can check 3 boxes for each VAT. With the IF function, you will check the value of 3 cells. You can also use the dropdown list and specify 3 VAT values in it. Then, in the formula, just use the value from the dropdown list.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  7. Hello, I'm working with a large amount of text in excel and trying to create a formula where text in one column (name of country) and text in another column (name of a political party) produces a specific pre-set code to identify a specific party. Dataset includes dozens of countries and hundreds of parties.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  8. I have a question I am using IF function to a cell, if the cell is FALSE, it will appear as blank. But when a date is inputted i want it to add 2 more days. Is that possible? or how can i do that?

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

      =IF(A1=FALSE,"",IF(CELL("format",A1)="D4",A1+2,""))

      You can learn more about CELL function in Excel in this article on our blog.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  9. I WANT A HELP
    IF i= 5
    in cell A1 it shall write A1
    in cell A2 it shall write A2
    in cell A3 it shall write A3
    in cell A4 it shall write A4
    in cell A5 it shall write A5

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      What does the condition i = 5 mean?

  10. Hi
    I have a problem to use IF statement to give me text value when a numeric value condition is met.
    Example:
    If cell "A" is more than "15" should give text "H" (High) in cell B, if value equals "5" but less than "15" should give "M" (Medium), and less than "5" should give "L" (Low).

    I will appreciate your help.
    Thanks

  11. I have an employee training log that goes back about three years. I need to know if and how I can write a formula in a different sheet to look at a list of required training, compare it to the training log and tell me on the new sheet what employee has not taken what training. Is this possible or are my hopes too high for excel?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  12. Good Morning!

    Hello Ma'am/Sir,

    I have some problems regarding date formula.

    =IF(EXACT($K9,"NOT DONE"),EXACT($K9,"DONE"),TODAY())

    That is the formula that i created if the value in the cell is "DONE" & "WIP" today Date will be shown in the cell. And if the value is different the cell value would be "NA" but the issue if i am putting next argument the excel showing an error "You've entered too many arguments for this function" this is the formula i created where error is showing =IF(EXACT($K9,"NOT DONE"),EXACT($K9,"DONE"),TODAY(),"NA") If you can help me with this it will be a big help.

    Another thing Ma'am & Sir,

    =IF(EXACT($K9,"NOT DONE"),EXACT($K9,"DONE"),TODAY()) for this formula once if i put the text "DONE" & "WIP" the today date will shown but the issue is once it is in the next day the next day date will be updated in the cell i want it like when i put Done and WIP today date will be there but if it is possible it will not change on the next day or the other date. Like when they put DONE or WIP in today date it will not change the date by tomorrow it self because it is updated once the date change by the next day.

    It will be a big help for me if you will answer it thanks and regards.

  13. Hi, I'm trying to use an if function to check if a string value matches it, it adds the corresponding numeric value to a cell but it doesn't work.

    =If(C10:C43="Income",J10:J43+H7, H7+0)

    So basically, I want the sheet to check that if Income is selected on the C range, the value from J is then added to the cell H7. This is for an expense tracker that I'm currently working on.

    I'm new to this and I really appreciate any help.

    1. Hello!
      In a cell like K10, you can use a formula

      =IF(C10="Income",$H$7+J10, $H$7)

      Then copy this formula down the column.
      I hope my advice will help you solve your task.

  14. Hi Can any one tell me how can i fill the Colour in Excel file. For text like Green , Yellow, Red. Etc.

    1. Just use conditional formatting

  15. how to write this function properly.

    =IF(J3 greater than 10,"1",IF(J3 is greater than 10 or less than 15,"2",IF(J3 greater than 15 or less than 20,"3",IF(J3 greater than 20 or less than 25,"4",IF(J3 greater than 25,"5")))))

  16. Hi,

    I need help in Excel cell. I want to fill the time only if if is not filled. To fill the time I am already using IF statement =IF(E2 "", NOW(), ""), so trying to solve with nested IF statements. But unfortunately it is not working.

    With statement =IF(E2 "", NOW(), "") - it is filled the value in Coloumn B2 with now time, but it if I enter in new row, the time in previous row get updated. Can you please help.
    Regards

    1. If you want the formula to fill in the blanks then it should be =IF(E2="", NOW(),B2)

      Now() function is dynamic so it will continuously update. If you want previous entries to stay static then you need to copy+paste as values.

  17. Hi,

    I need help in Excel cell. I want to fill the time only if if is not filled. To fill the time I am already using IF statement =IF(E2 "", NOW(), ""), so trying to solve with nested IF statements. But unfortunately it is not working.

    With statement =IF(E2 "", NOW(), "") - it is filled the value in Coloumn B2 with now time, but it if I enter in new row, the time in previous row get updated. Can you please help.

    Regards, Surinder

    1. Hello,

      I'm creating a Sales order form where I use the following formula -
      =if(and(F18>0, D18>0, E18>0),C18*D18*E18*(1-F18),"")

      So what I'm trying to do is to multiply cells C18 through E18 and if there is a discount amount in cell F18, then include it and get the final net price which includes the discount amount. This is fine and works.

      What I can't make work is to multiply cells C18 through E18, but if there's no discount amount (cell E18 is left blank), I would still get the net price without the discount code.

      I hope this makes sense! Any help would be greatly appreciated!

      Thanks!

  18. i need help if formula
    tax slab is Rs. 0 - 250000 = 0
    next 250000 - 500000=5%
    next 500000 - 750000=10%
    next 750000 - 1000000=15%
    next 1000000-1250000=20%
    next 1250000-1500000=25%
    next 1500000 above 30%

    if my income is 875000 or 1420000
    please send formula,
    =IF(K3>500000,250000,0)*5%+IF(K3>750000,500000,IF(K3>500000,K3-500000))*10%+IF(K3>1000000,750000,IF(K3>750000,K3-750000))*15%+IF(K3>1250000,1000000,IF(K3>1000000,K3-1000000))*20%+IF(K3>1500000,1250000,IF(K3>1250000,K3-1250000))*25%+IF(K3>1500000,(K3-1500000),0)*30%
    this formula is not proper working after 10%

  19. IF AND OR?

    E4 is the sum of points a team gets as per awarded in A1:A3. If Team Bob is in A1 they get 7 pts. If in A2 they get 5 pts. If in A3 they get 2 pts. Ergo, possible totals are 2,5,7,9,12,14 points.

    For cell E4:
    IF(A1="Bob",A2="",A3="", 7)
    IF(A1="Bob",A2="Bob",A3="", 12)
    IF(A1="Bob",A2="Bob",A3="Bob", 14)
    IF(A1="Bob",A2="",A3="Bob", 9)
    IF(A1="",A2="Bob",A3="", 5)
    IF(A1="",A2="Bob",A3="Bob", 7)
    IF(A1="",A2="",A3="Bob", 2)

    IF(A1="",A2="",A3="", 2)

    Last statement is to say that if NONE of the above is true, enter "0" at E4

    The same calculation would occur for 4 other team: Carol(E5), Ted(E6), Alice(E7), Sam(E8)

    Thanks!

    1. Could not edit to a note here... my "last statement" above should be IF(A1="",A2="",A3="", 0)

  20. Hello,
    Everything I know about excel has been self taught and it's been so long that I'm going crosseyed trying to figure out what I need to do. I've downloaded a set of sheets that was designed by another person who obviously knew excel very well. It is intended for the creation of characters for a table role playing game. However I found that he missed a very basic game rule in his design and I'd like to add it. My issue is that I just can't seem to wrap my head around the formula. Without looking at the entire workbook, I'm not sure if you can help me.
    Here is the current formula in the cell that I am trying to change.

    =IF($CR3,"—",SUM(IF(AND(WildShape,WildshapeCell>1),WildShapeStr,IF(AND(Race="Hengeyokai",HengeyokaiAnimal"",HengeyokaiForm="Animal"),VLOOKUP(Subrace,TblCreatureInfo,CreatureStrCol),InitialStr)),$C3:$BL3,INDEX(BuffsAbilitiesAdj,$CD3)))

    Without context I'm sure that makes little sense aside from the layout of the formula. What I want to do is add in the following.
    If Race=human and CharacterAge is >=35, >=53, >=70 then add or subtract specified amount from the value in the cell dependent upon which of the number ranges the CharacterAge falls within.

    As I said, without looking at the sheet yourselves, I'm not sure if you can assist. Anything you can do would be fantastic!

    Thanks,
    NAt

  21. Hoping this makes sense :)

    I have row D calculating =B11+365 and showing a date a warning would expire

    I'd like the that date to only show up/ calculate if I8 ="y" -- otherwise I want D shwoing no dates.

  22. Hi, can you help me to find formula like this =IF(E15211<Q15211;"R.S";"INDENT") but the problem is E15211 still empty but has shown "Indent" on it.
    it is possible to make it blank until i put a data?

  23. Is there any formula to return a picture that is in e.g. C2 yo be auto copied into E3. I dnt want to manually paste the same picture into cells where the pocture is needed!!

  24. I am looking for help with a MULTIPY IF formula. i have a list in cell C17 that has 7 text options. i would like to have a cell be the product of 2 other cells if one text selection is made. if a different text selection is made, then I would like the product of a different set of cells to be displayed.

    I hope that's detailed enough.
    thank you in advance

  25. hi i want a formula to check from another sheet if the specific date got order
    but below formula is not working, 5/12/2020 should have order but it show no order

    =IF('Order List'!C4:C125=DATEVALUE("5/12/2020"), "GOT ORDER", "NO ORDER" )..

    1. 1. Make sure the format date is same as you use on your desktop.

      2. delete space on =IF('Order List'!C4:C125=DATEVALUE("5/12/2020"),here"GOT ORDER",here"NO ORDER"here)

      Hope this help out

  26. Good Morning,
    Could really use your help on this one. I want to return a text - TBD based on another cell that has a formula. If the formula produces a date, the cell would be blank. If it does not produce a date, the cell would then be TBD

  27. A1 contains date and B1 contains date. i want to use if function as below and get the return as "it is implemented from XX-XX-XXXX(the date in A1)"
    =if(A1<B1,"it is implemented from" A1,"")

  28. A1 contains date and B1 contains date. i want to use if function as below and get the return as "it is implemented from XX-XX-XXXX(the date in A1)"
    =if(A1<B1,"it is implemented from" A1,"")

  29. Hello
    i am trying to get my sheet to recognise when a new cell is added to the data and to pick the information in that cell as the new summary.
    so if A1 is the summary
    A5 = 2 at the moment A1 picks its data from A5
    and i input in A6, a new value, say 3 so A6 =3.
    I want A1 to dis regard A5 and pick data from A6 automatically?
    cay you help me?

  30. how to get if A1="B" then output Tx=0.075*h^.75 and Ty= 0.085*h&^.75??

  31. Cell A1 contains fund codes, which usually start with a letter (e.g TWDK1D). But sometimes, A1 contains funcd codes that start with "4", where TWDK1D is 4TWDK1D instead. If the cell value of A1 starts with "4", I want Excel to only populate the rest of the fund code and exclude the "4". How do I do this?

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

      =IF(LEFT(A1,1)="4",REPLACE(A1,1,1,""),A1)

      Hope this is what you need.

      1. It worked perfectly. Thank you very much @Alexander for your help.

        Truly appreciated.

  32. Добрый день, пожалуйста, помогите, есть такая таблица , и мне надо в последнем столбце "категория роста" вывести значения- высокий, средний или низкий в зависимости от пола и роста,в самом низу градация
    я написала выражение- =IF(AND(D15 = "ж", E15>170), "высокий") ,IF(AND( D15= "ж", E15 178),"высокий"),IF(AND( D15= "м", E15178 см >=168 см <=178 см 170 см >=150 см <=170 см <150 см

  33. Hi I wanted to use Farmuale as IF(E3=E2),CONCATENATE(B2&B1),B1) But reult showing up error. Can some one help inthis regards ?

  34. Hi, help?

    If A1= Yes and B1 = Yes then return Complete
    If A1= Yes and B1 = No then return Overdue
    If A1= No and B1 = No then return In Progress

    M

  35. I am working in a simple spreadsheet but would like for the whole IF-Then argument equation (if True) be returned AND display the answer.
    " x [0.06600] = 0.06600" assuming the column containing the x has a 1.
    "x [0.18200] = 0.72800" if the column containing the x had a 4.
    "x [0.00030] = 0.05100" if the column containing the x was 17.

    So, I'd like for the whole text phrase of "x [0.00030] =" to appear along with the answer value "0.05100" as indicated

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

      =IF(A1=1,"x [0.06600] = 0.06600",IF(A1=4,"x [0.18200] = 0.72800", IF(A1=17,"x [0.00030] = 0.05100","")) )

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  36. Greater than 11 its a YES
    Less than 11 its a NO
    The cell is blank its a UNKNOWN

    1. Hello!
      I recommend reading this article on nested IF functions.
      Please use the following formula —

      =IF(D9="","Unknown", IF(D9 > 11,"Yes","No"))

      or

      =IF(ISBLANK(D9),"Unknown", IF(D9 > 11,"Yes","No"))

      I hope it’ll be helpful.

  37. Hello!

    I've been racking my brain trying to work this formula out but am at a loss and was hoping you'd please help.

    What I'm trying to achieve is IF the cell equals >11 its a YES, IF its 11,"YES","NO", IF(D9="","UNKNOWN))

    Thanks in advance!

    1. What I'm trying to achieve is IF the cell equals >11 its a YES, IF its 11,"YES","NO", IF(D9="","UNKNOWN))

  38. I have a variable creatinine which contains a numerical value. I want to show the variable if the value is less than 4, else if it is more than 4, then display 4.

    =IF(Creatinine > 4, 4, Creatinine)
    This formula is displaying 0 & 1 if the condition is false or true respectively.
    What am I doing wrong?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. What is "Creatinine"? If you use regular cell references -

      =IF(A1 > 4, 4, A1)

  39. Hello,

    After doing the IF syntax and getting the 1's or 0's as a result, how do we get to know that how many number of 1's and 0's are there if there are lot of values to be checked.

    Thanks in advance!

    Regards

  40. Hi,

    Sorry, I am a bit confused with the nested Isnumber on IF (Example 3) in text values.

    I understand ISNUMBER function in Excel evaluate if a cell contains a numerical value or not, and returns True or false.

    IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")

    Now, In this case, from inside, I would expect the SEARCH to return all "deliv*", and feed into ISNUMBER and get checked if such value is numeric then input (True or False) into IF function.

    You have explained well what SEARCH/FIND functions perform in evaluating text values. I do understand that in this particular case the ISNUMBER is a sort of "helper" (bridge).

    Could you please explain the outcome of this expression ISNUMBER(SEARCH("deliv",C2)) and how it is evaluated at the end..

    Thank you for your valuable time.

    1. Hi Afonso,

      I think you have explained almost everything yourself :) I will just clarify about the SEARCH function. It returns the position of the first occurrence of a given character or substring within a text string. In example 3, cell C2 contains "Delivered", so SEARCH("deliv",C2) returns 1. If C2 contained "item delivered", then SEARCH would return 6. If C2 contained "deliverable delivered", then SEARCH would return 1 again (the position of the first found match). For cells that do not contain "*deliv*", SEARCH returns a #VALUE error.

      Like you said, ISNUMBER evaluates the SEARCH output. If it's numeric (i.e. "deliv" is found in C2, no matter in which position), ISNUMBER returns TRUE, and IF applies value_if_true ("No" in our case). If SEARCH results in an error, ISNUMBER returns FALSE, and IF applies value_if_false ("Yes" in our case).

      I hope I answered your question. If something is still unclear, please feel free to ask.

  41. S.No Vehicle-Reg-No Rept-Date Rept: Time Entry Date In Time In Date Out Time Out
    1 GLT5739 02-Nov-20 19:20 03-Nov-20 21:50 04-Nov-20 23:40

    I want to make below report from my above excel sheet.
    1.In side the plant More than 12 hours Vehicles.
    2.Out side the plant More than 12 hours Vehicles.

  42. If the 1st 2 digit of the number in a cell is 11, i need to be rename as Import in another cell and if it is 10 , it has to be rename as Local , that is my requirement..please help....

  43. can you help me for this formula
    A Coolum contains 1) Acc repairs 2)running repairs B coolum contains value in c coolum contains Value if
    i want formula in d coolum if a running repairs value of b coolum if a coolum contains acc repairs then B-C

  44. Hi,

    Hope you are well, wonder if you can help me;

    I am trying to calculate; if CELL 1 has a date > than the date in CELL 2 then calculate days over (currently using =IF(F7>L7,F7-L7,0) but i want to add, if CELL 1 is blank then still calculate days over using todays date - CELL2.

    The current formula only works out the days over if a date has been inputted in CELL 1, but i need to use AND/OR condition but i cant get it work. (Date in cell 2 is another formula counting 1 year on from another date used)

    Hope this makes sense...

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

      =IF(ISBLANK(F7),TODAY()-L7,IF(F7>L7,F7-L7,0))

      I hope my advice will help you solve your task.

  45. I am working on a COVID spreadsheet. I have two columns. One for a Symptom On Set Date and one for an exposure date. I need a 3rd column for the Return Date. This date is +11 or +15 based on if it was onset of symptoms or exposure. I want to create an IF Then statement that is something like...
    =IF On set Date is true then add 11 to the cell of on set date or if exposure date is true then add 15 to give me a return date.

    I tried =IF(J2_True,"J2+11",K2_true,"k2+15")

    J2 is my On set date and K2 is my exposure date. It obviously is incorrect. Can you help me? Thank you.

  46. hi, i need help to search in column about value and return raw number.

  47. Hi
    Hope someone can help!
    I am trying to run a formula that will place text in a column based on a upcoming date in another column. For example: If Go live is within 3 business days, (this is column j, with a date of Nov 1) then place "at risk" in column k. Another example. If Go live is within 7 business days, place "upcoming" in column k. The problem is the dates are fluid and I don't want to have to change the datevalue every time the date changes.
    Hope this makes sense.
    Thanks
    Natalie

  48. Thanks for the post.

    I'd like some help on my personal project that involves calculating savings on my savings account. What I like would be to auto calculate on specific dates when my pay comes in and the excel calculates it automatically with any input form me. Thanks.

  49. Is there a formula to find the cell value between two numbers?

  50. Hi, I'm looking for an IF formula that will return HAPPY if the score is greater than or equals 15 and SAD if it is less than 15.

    Thank you.

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

      IF(A1>=15,"HAPPY","SAD")

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