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 21. Total comments: 4823

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi
    I have multiple documents which need to be diffracted using formula

    I want a formula for the Below type.,

    If i have the below documents types., help me with the formula in "__"

    CE It should show "PAYMENTS"
    CM It should show "PAYMENTS"
    RV It should show "INVOICE"
    RR It should show "INCENTIVE"

    Please help me..Appreciate your time and efforts.

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  32. Hello There,

    Im having trouble coming up with the Formula to calculate that If

    A1 has a Invoice Issue Date in and B1 Is to have the Payment received date in but is currently blank.

    how can I have it show me in C1 how many days it has been since the invoice was sent. Up until the point when I have the payment date and then I would like it to stop the count and show me how many days it was between start and finish.

    so to basically have an open counter in days until a final date is put in.

    fingers crossed one of you can help as this has been frying my brain all morning I'm sure its not as complicated as I'm making it out but it has gone beyond me. Many Thanks in advance

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(B1<>"",B1-A1,TODAY()-A1)

      I hope it’ll be helpful.

      1. Thank-you very much first time. And all works perfect. Many Thanks

  33. Hey all. I would like my formula to do the following:

    If a value in 'Table135 Column 2' equals a value in 'Besteltabel Column 1' and there is a value in 'Table135 Column 1' and there as a value in 'Table135 Column 7' and there is no value in 'Table135 Column 8' Then I want it to say yes... if not, I want it to say no.

    The current formula I have doesnt work, but should be along the lines of what Im looking for. Can someone help me out??

    Current formula:
    =IF(Table135[Artikelnummer]=(Besteltabel[[#All];[Artikelnummer]])&(Table135[Datum bestelling]=TRUE)&(Table135[Datum levering]=FALSE)&(Table135[Aantal besteld]=TRUE);"JA";"NEE")

    Thanks in advance.

    1. Oh forgot to mention the following:

      Table 135 - Column 2 = Artikelnummer
      Table 135 - Column 1 = Datum bestelling
      Besteltabel Column 1 = Artikelnummer
      Table 135 - Column 7 = Aantal besteld
      Table 135 - Column 8 = Datum levering

      1. Hello!
        It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
        Unfortunately, without seeing your data it is impossible to give you advice.
        I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
        We'll look into your task and try to help.

        1. Thanks for the response! I've sent you an e-mail.

  34. Hi, please help. what's the formula - I have three columns, if these three has a value, it will appear as completed, if either column 1 and 2 with column 3 has a value, it will appear completed, if only column 3 but there's no value for column 1 and 2, it will appear almost completed.

    another, how can i count the cells with formula but only has value?

    Hope you can help me.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. column1 column 2 column 3
        938,080 1 6001371
        0 1 6001481
        938,080 0 -

        like this one sir, if there's a value in column 1 or 2 then also with column 3, its will appear as completed. But if there's a value in column 1 and 2 and there's none in column 3, it will appear as not completed or vice versa, if column 3 has value and column 1 and 2 has none, it also appear as not completed.

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

          =IF(SUM(IF(A1:C1<>"",COLUMN(A1:C1)))>3,"Completed","Not completed")

          I hope my advice will help you solve your task.

        2. another thing, how can i count the column if there's only 1 on it. it will not count if there's formula but it's zero.

  35. 2. Bob receives a bonus of $50 from the d20 manufacturer each time his sales exceed $500 in a quarter. Write a conditional statement (IF) in cell B11 that will display 50 if the d20 sales are greater than $500 and 0 otherwise. Once you have the formula correct, copy it into C11-E11.

  36. Is there a way to write a formula that says that if a cell has a certain name in it, that I want a series of cells populated (for example - if A2 = SCHOOL then fill B2; AA2)?

    1. Hello!
      In cell B2, you can write the formula

      =IF($A$2="SCHOOL","SCHOOL","")

      Then copy the formula to other cells in your range.

  37. Hello,
    I have the following formula =IF(COUNTA(C8:C12)=5,IF(AND(MIN(C8:C12)>0.999,MAX(C8:C12)<4),"Pass","Fail"),"INCOMPLETE")
    It looks at the data gives "incomplete" if any data is missing, if all Pass, I get a "Pass" result, and if one Fails I get a "Fails" result. However, I would like for the Result to change from "incomplete" to "fails "as soon as one data point Fails. I don't want to wait to take all 5 data points to change from Incomplete to Fails, and if no data points are failing I would like for it to stay as incomplete.
    I hope I explained myself correctly.
    Thanks!

    1. Hello!
      Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  38. I have the following formula put in and it works: =IF(G2="PAPR", "X")
    All cells in the column (Column F) contain dates, except where no date is needed, then and X is required, based on if the cell to the right in Column G contains "PAPR" or not. My issue is when I go to drag the formula down in Column F, the dates in the cells disappear and turn to FALSE. How can I apply the formula to all cells in Column F containing dates without the dates disappearing? Thanks!

    1. Hello!
      In a cell, you can write either a value (date) or a formula. You are changing the date to a formula. To conditionally highlight cells, I recommend using conditional formatting.
      If there is anything else I can help you with, please let me know.

  39. Reference No Letter of Credit Bank - Cash 375,000
    Due Date Amount_AED Due Date Amount_AED Over Draft Bank (25,000)
    26,000 15-Oct-20 26,000 15-Oct-20 10,000 Bank Balance 350,000
    28,000 16-Oct-20 28,000 16-Oct-20 14,000
    30,000 17-Oct-20 30,000 17-Oct-20 16,000
    32,000 18-Oct-20 32,000 18-Oct-20 18,000 Salary 90,000
    34,000 19-Oct-20 34,000 19-Oct-20 20,000 Supplier Payments 85,000
    36,000 20-Oct-20 36,000 20-Oct-20 22,000 Dewa 25,000
    38,000 21-Oct-20 38,000 21-Oct-20 24,000 TR 186,000
    40,000 22-Oct-20 40,000 22-Oct-20 26,000 LC 100,000
    42,000 23-Oct-20 42,000 23-Oct-20 28,000 Totl Expences/Liabilites 486,000
    44,000 24-Oct-20 44,000 24-Oct-20 30,000 Reserve (136,000)
    46,000 25-Oct-20 46,000 25-Oct-20 32,000
    48,000 26-Oct-20 48,000 26-Oct-20 34,000
    50,000 27-Oct-20 50,000 27-Oct-20 36,000
    52,000 28-Oct-20 52,000 28-Oct-20 38,000
    54,000 29-Oct-20 54,000 29-Oct-20 40,000
    186,000 100,000 20-Oct-20 286,000

    Need to know the date when my reserve becomes 0 so that we can maintain cash flow based on above example

  40. Hi,
    I'm trying to write a statement that expresses the following:
    C2=A
    D2=G
    E2=Absent
    If cell E2=Absent, then C2/C2 or A/A in this instance but it could be various letters in C2 or D2. If E2=heterozygous, then C2/D2 or A/G in this instance.

    1. Hello!
      Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  41. Good day,
    Trying to trim multiple lists of commercial names to common names. Something like xxxxxx™ yyyyyy® zzzzzzzz where xxxxxx would be the common name. Most often ™ comes after the xxxxxx and I can use =TRIM(LEFT(D3,(FIND("™",D3)-1))) but sometimes the ® will be after the xxxxxx. Trying to use an IF formula to find if there is a TM after xxxxxx gives a #VALUE! error. Is there a way to use the #VALUE! as a condition in an IF formula?

    1. Hello!
      I recommend using the SUBSTITUTE function to remove characters

      =TRIM(SUBSTITUTE(SUBSTITUTE(D3,"™",""),"®",""))

      I hope my advice will help you solve your task.

    2. Switched to using IFERROR function and got it to work

  42. i have an issue i want to make an if statement says return 0 tax if the category is ''food'' and return 20% of the amount if the category is not ''food''. Heres how i have written my formular: =IF(C2=''Food'',0,0.2*D2)
    But it has failed to work. its saying invalid on my logical statement (C2=''Food'') but the rest are okay.

    1. Hello!
      Your task is not completely clear to me. The formula is written correctly. 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.

  43. Hi,

    I am looking for a formula which count unique id numbers in multiple rows (for example 5 rows of same id number) however want it to represent 1 for the first record and then 0 for each other (so the unique id is counted as one in the data set).

  44. Im trying to set up a responsibility sheet, cells in Colum A are names chosen from a drop list based from separate contact list. the cells in colum B will be phone numbers. But I want the phone numbers to auto populate based off the name I type in colum A. ie. if A1= Smith B1= 555-0213 . My contact list is a separate sheet in the same workbook.

  45. Hi Experts,
    I am looking for the Customised display with runtime calculated value.
    E.g =IF(I3>0,"Ahead By I3%",IF(I3<0,"Behind By I3%","On Track"))
    Here i want Actual value of I3, instead of String value. Please let me know how to resolve it.

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(I3>0,”Ahead By "& I3 &"%",IF(I3<0,"Behind By "& I3 &"%","On Track"))

      About "&" read this asticle

  46. Hi,

    Thanks for the awesome work!

    I am looking to proiduce a report which only displays a row if there is a value in a range of three (3) cells, so as to eliminate having to scroll through pages of blank data and to minimise my report length.

    Any suggestions?

    Any and all help you offer will be greatly appreciated!

    Kind Regards,
    David

    1. Hello!
      You cannot hide a row using an Excel formula. You need to use VBA macro. If you hide such a line with VBA, you can never write data to it.

  47. Hi,
    Can anyone guide me to correct this formula:

    =IF A2 is >=-107 and A2 is also =-104 and =-100 type High

    Thank You

  48. Hi,
    Can anyone guide me to correct this formula:

    =IF(G2>=-107&G2=-104&G2=-100,"High")))

    Thank You

  49. I would to get a formula that gives 1 if given data in a given cell is more than a specified number. E.g If B1>1600, then show 1

  50. Hi i am trying to write a formula =IF(I2-G2>=3,"HO", IF(I2-G2<4, "CO")) and i want a third option if the cells are blank it will display "-"

    1. Hello!
      Please use the following formula:

      =IF(I2-G2>=3,"HO", IF(I2-G2<4, "CO", IF(AND(I2="",G2=""),"-")))

      Hope this is what you need.

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