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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. 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. Thanks for the response! I've sent you an e-mail.

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

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

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

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

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

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

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

    Thank You

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

  25. Hello ,applied vlookup to the cells got N/A in multiple cells i use it as values but instead of N/A previous value to fetch and when my cell found new value formula starts from next cells (Which fetch new value as previous value)
    =IF(AND(H:H="#N/A",H3-1),"",IF(H:H=" ",H3))

    1. Resolve this by using IFNA(H3,I2)

  26. Hi, I was trying to ask you a question about adding numbers of certain Rows of a Column if the those Row has a certain keyword. For example column B has the income and Column C has the method(cash or online). and so if i were to sort the income to cash payment and online payment where it would add the column B's Certain Row if that Row's Column C has "Cash" in it.

    1. Hello!
      For me to understand your request better and find a solution, please describe your problem in more detail. Include an example of the source data and the result you want to get. Thank you.

  27. Hello,
    I'm using the following formula, where I want 30, 20, 10 to appear in the cell as money:
    =IF(C7>=90,"30",IF(C7>=80,"20",IF(C7>=70,"10")))

    After entering the formula I used the $ / currency "button" to change the cell to a dollar amount but it doesn't work. Do I need to add it into the formula? If so, how?

    Thank you!!

    1. And one more question, the above formula =IF(C7>=90,"30",IF(C7>=80,"20",IF(C7>=70,"10"))) shows FALSE in the cell until data is entered in C7. How can i show the cell as blank until data is entered?
      Thank you!!

      1. Hello!
        To use the monetary number format, your formula must return numbers, not text as it is now.
        Change the formula

        =IF(C7>=90,30,IF(C7>=80,20,IF(C7>=70,10,"")))

        I hope my advice will help you solve your task.

        1. It worked perfectly, thank you for your help!!

  28. Hi i am trying to create a formula which will distinguish high, critical orders as well as express air delivery methods, Now i only want TRUE in next column if both the columns have any one of the text in them.

    Order Priority Ship Mode Customers who are urgent
    High Regular Air
    High Express Air
    Critical Express Air
    High Delivery Truck
    Critical Regular Air
    Medium Express Air
    Low Regular Air

    I have been trying the simple if function to show only high or critical. but if order priority is medium or low and ship mode is express air. it shows false. kindly solve my problem.

    1. Hello!
      To check if both columns have text, use the formula

      =IF(AND(A1<>"",B1<>""),TRUE,FALSE)

      or

      =IF(AND(ISBLANK(A1),ISBLANK(B1)),FALSE,TRUE)

  29. can you help me!

    What's the formula if a specific number or text will only appear in the cell?

    sample : only number 1, will appear on the cell. IF I PUT OTHER NUMBER IT WILL BECOME ERROR.

    THANK YOU

  30. Hello,
    I'd like my IF statement to say:
    If AN23=KS, then type in 6/30/2021, otherwise type in 9/28/2020

    AN23 is linked to another cell and either says KS or MO
    So, the formula should place either 6/30/2021 or 9/28/2020 which are the expiration dates of a state license.

    =IF(AN23="KS",DATEVALUE["6/30/2021"], DATEVALUE [9/28/2020])

    This is what I typed and it doesn't work.

    Thank-you.

    1. Hello!
      Please try the following formula:

      =IF(AN23="KS",DATEVALUE("6/30/2021"), DATEVALUE("9/28/2020"))

  31. Please i want to know how to use IF function to determine or return only each month names (e.g January , February etc) from a single column of different dates

    1. Hello!
      Your IF formula will be very large. I recommend using the VLOOKUP function to select the name of the month

      =VLOOKUP(MONTH(B1), {1,"January";2,"February";3,"March";4,"April";5, "May";6,"June";7,"July";8,"August";9,"September";10, "October";11,"November";12,"December"}, 2,0)

      I hope this will help

  32. Please assist with this formula, especially with the last criteria. If a student is absent during test week and needs to show up on the Remark Column.
    =IF(W5>84,"An excellent performance. Keep it up!.",IF(W5>64,"A very good performance. Can still improve.",IF(W5>49,"A good performance. There is room for improvement.",IF(W5<50,"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.")))))

    1. Hello!
      Add a condition to your formula that W5 is a number.

      =IF(AND(ISNUMBER(W5),W5>84),"An excellent performance. Keep it up!.", IF(AND(ISNUMBER(W5),W5>64),"A very good performance. Can still improve.", IF(AND(ISNUMBER(W5),W5>49),"A good performance. There is room for improvement.", IF(AND(ISNUMBER(W5),W5<50),"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.","")))))

      I hope my advice will help you solve your task.

  33. i have a age list of stuents in column C . age is in yearsmonths and days i.e. 14years,11monts.20 days etc .I wants to calulate under age and over age on a perticular date i.e 01 april 2020. whoes age 1s more then 16 years "overage" and less then 15 year"underage" how can i solve this?

  34. HELLO!
    I am trying to write a formula to fill a cell (say T9) with N/A if the cell J9 includes wording "Standard Type I" or "Standard Type II". The field selections in J9 include but are not limited to
    MBCI Standard Type I 20yr
    MBCI Standard Type II 20yr
    MBCI Single Soucre III 20yr
    and so on

    1. Hello!
      Your condition can be written into a formula

      =IF(OR(ISNUMBER(FIND("Standard Type I",J9,1)),ISNUMBER(FIND("Standard Type II",J9,1))),"N/A","")

      I hope this will help

  35. Hi,
    My problem is i have a column full of times in 24hr time and need to categorize these times into 4 different categories in a separate column (2,3,4,5). i cant figure out the IF function to do this.
    for example the first category would be times between 0:00:00AM - 6:00:00Am would be category 2
    Thanks,

    1. Hello!
      To convert time to number, use the formula

      =A1*24

      You can use these numbers in the IF function to create conditions.
      I hope my advice will help you solve your task.

  36. hie i wanted if its possible to use the IF function in excel to check whether in a particular column the cell have data that is in cell format or not. all the function i have tried so far give a specific date. i just want it to verify if the cell had data that in date format that's it.

    1. Hello!
      To check if a cell is written as a date or just a number or text, you can use

      =LEFT(@CELL("format",A1),1)="D"

      I hope this will help

      1. Yes this was a great help, thanx

  37. How to use if function in between the numbers. Eg 8am to 8pm peak, and 8pm to 8am off-peak. What is the formula

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

      =IF(AND(C1*24>8,C1*24<20),"peak","off-peak")

      Hope this is what you need.

  38. Hello, I am wanting to create a formula that if the value is greater than 0 then the result displays the value but if is is 0 then it displays 'unknown'. Is this possible? Thanks

    1. IF( A1 > 0, A1, "unknown")

  39. I have to produce a spreadsheet for covid19 weekly testing of staff. I want to place next due date in cell after entering y in tested cell calculating 7 days ahead from date tested. A1 =date tested, B1 =y for tested C1 = due date by 7 days. Could you assist with formula
    Thanks

    1. Hi Stuart.

      Try the following in cell C1

      =IF(B1="Y",A1+7,"")

      the "" will show as a blank cell Also dont forget to format cells to dates.

  40. Hello,
    I want a formula that check another cell if it contains certain text and just come up with the today's date if find this text.
    Eg.: ( if cell A contains ''sent to client'' the result is 27/07/2020)

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

      =IF(A3="sent to client",DATE(2020,7,27),"")

      or

      =IF(A3="sent to client",today(),"")

      I hope this will help

  41. Hello!
    My knowledge of Excel is basic at best. Is there a way to sort data alphabetically into another worksheet? For example, if I have a list of names on Sheet 1, can I then sort that info into other sheets broken down into parts of the alphabet? EG, Sheet2 = A - H; Sheet3 = I-P; Sheet4 = Q-Z. I'm trying to create a workload list for my staff but their work is divided by alphabet. Thank you.

  42. I'm a layman trying to set-up an excel worksheet and hoping someone would be able to tell me how I would write the following formula for a dollar amount;
    If cell 7 + cell 9 is > cell 10, then cell 14 = cell 7; If not, then cell 14 = cell 10 - cell 9.

  43. Hello,
    is it somehow possible to have both text and a formula as value_if_true/false? Simple example what I mean and what is wrong =IF(A1-B1=0;"OK";A1-B1 "PIECES MISSING")??
    Thanks

    1. Hello Jan!
      You did not describe your problem very accurately. I'll try to guess.
      Perhaps you wanted to write down such a formula

      =IF(A1-B1=0;"OK";(A1-B1)&" PIECES MISSING")

  44. hello,
    i want to know the formula to calculate the diffrence in time to calculate lateness for staff.
    time in is 07:30 am
    time out is 17:00 on monday only
    time out is 16:45 tuesday to friday
    how can i calculate the overtime, lateness and for the early out.
    can you please provide me a formula.
    thanks in advance
    Regards,
    Krish

  45. Hi Alexander,
    I am trying to get a Region based on two condtions: Animal and Month.
    You choose animal in I3 and month in K3.
    The list of animals is in D6:D15, the list of months is in E6:E15, the list of regions is in F6:F15.
    Multiple animals and months can appear at the same time, in two different regions.
    However, the code I am trying to enter does not return the region as I want.
    =IF(AND(D6:D15=I3;E6:E15=K3);"ok";"fail")

    It's a rather simple code, but I just can't seem to get it to work.

    Hope you have an idea. Thanks! :)

  46. well i can't change some number like 1-1.000.000.000.000,00 into a text like:
    234.567.891 into (Dua Ratus Tiga Puluh Empat Juta Lima Ratus Enam Puluh Tujuh Ribu Delapan Ratus Sembilan Puluh Satu - indonesian) or (Two Hundred thirty Four million Five hundred Sixty Seven Eight Hunred Ninety One - english).
    will someone help me?

  47. Hi Alexander,
    I'm trying to write a countif formula to only count the "PO-B" positions I have for my department and exclude the open ones or if they don't contain "PO-B".
    DATA
    PO-B-1 - Eisenhuth, Rebecca
    PO-B-17 - OPEN
    M-B-1 - O'Banion, Ruth Ann
    QC-B-1 - Thioune, Omar

    Thanks in advance

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

      =SUM(--IFERROR((SEARCH("PO-B",$E$1:$E$28,1)>0),0)*(--ISERROR((SEARCH("OPEN",$E$1:$E$28,1)>0))))

      I hope this will help

  48. Hi Alexander,
    I am not able to formulate given below conditions with IF statements. My query is associated with number of questions, which I need to bind with time. I have no idea how to do formulate with IF conditions.

    Condition-1:
    If I type (greater than 10 questions but less than 16 questions) in 30 minutes then I will be getting 5 marks.

    Condition-2:
    If I type (greater than 5 questions but less than 10 questions) in 20 minutes then I will be getting 3 marks.

    Condition-3:
    If I type (greater than 2 questions but less than 5 questions) in 10 minutes then I will be getting 1 marks.

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

      =IF(AND(OR(A1>10,A1<16),B15,A1<10),B12,A1<5),B1<10),1, 0)))

  49. a. insert a formula using the IF function that tests whether the age of the invoice is greater than 30.
    b. If the age of the invoice is greater than 30, subtract the due date from the current date.
    c. If the age of the invoice is less than or equal to 30, display 0 to show that the invoice is not overdue.

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

      =IF(TODAY()-A1>30,TODAY()-A1,0)

      I hope it’ll be helpful.

  50. Hi i'm trying to put an IF statement into a cell to show the amount of a transaction under the right category heading. So far I have =IF(D10="Office Equip.",C10,"") so it inputs the value in C10 but I need one IF statement specifically to cover a range of columns from I:AH all with different headings. How would I type this IF formula?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)