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

  1. Hello,

    I’m having an issue. I’m not sure if this should be a nested IF statements. The first 2 formulas represent the subtraction of dates. The dates can equal 0 to represent no days have passed/a solution was made the same day. I do not want the 0 to populate, I want the cell to remain blank.

    1. =IF(ISBLANK(N10),"",(N10-F10)) This formula subtracts & shows 0 in a following cell. Example 3 formula is calculated properly & all values are showing thereafter in other columns.

    2. =IF(N10-F10=0,"",N10-F10) This formula subtracts & removed the 0 (as desired). Example 3 formula is NOT calculated when the 0 is removed. It then throws off 3 calculations for all other columns & rows to follow.

    3. = IFERROR(IF(ISBLANK(R10),””,(H10*R10)+(U10*V10)),""). This is the formula of the calculated column that is affected perfectly when Example 1 preceded it (non desired 0 in cell) but horribly when Example 2 preceded it (desired blank cell, no 0). I believe the multiplication is throwing it off & the removal of the 0 in Example 2 is causing it. V10 is used to calculate a formula in W10 ONLY when U10 & V10 have values. They will usually be blank.

    Is there a way to put a formula in that will distinguish when to use the “+(U10*V10) like an either or? I only need the second half of that equation when U & V have values. I hope this makes sense...

    1. Hello!
      I don't quite understand your calculations. It would be easier to understand them if you wrote an example of the source data and the expected result. The condition "when U & V have values" can be written as

      U10&V10=""

      It can be used in an IF function.

      1. Sorry I did another question instead of replying but I just figured it out! Thanks for your continued helpfulness, Alexander!

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

  3. I need to work out:

    If a number of years worked is less than 5, then 0
    If a number of years worked is more than 5, then add 1 for every year above 5

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =IF(B1<5,0,(B1-5))

  4. Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
    Info:
    9AM is 09:00 ( 9/24)
    5PM is 17:00 (17/24)
    From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
    #1 After 5PM show Tomorrow 9AM
    IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
    #2 After 17:xx show Tomorrow 09:xx
    IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
    # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

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

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

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

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

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

  10. Hi
    I'm trying to do a check in a spreadsheet and the the IF function is giving me the incorrect result:
    If(D200=AE200,"YES", "NO")
    D200 (this is a formula value of running balance D199 + C200) and AE200 (this is sum of F200:AD200). The value is the same in each cell i.e. 6,603.16 - but gives me a NO result.
    I've tried adding in VALUE before the cell reference but this still does not give me the correct result.

    1. Hello!
      Without seeing your data it hard to give you advice.
      Your numbers may differ in some decimal place. I recommend using the rounding function. Or you can perform calculations with the precision with which the numbers appear in your spreadsheet. To do this, use File-Options-Advanced-When calculating ...- Set pricision as displayed.

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

  12. I want to know the formula to use for time that is greater than 3:00:01

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Please specify what you were trying to find. Write an example of the source data and the result you want to get.

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

  14. I have 2 spreadsheets which are orders and deliveries.
    I want to have the order sheet updated with a Y in the received column when the delivery is received.
    Can I do this through a IF formula?

    1. Hello!
      Without seeing your data it hard to give you advice. If your data is in 2 different tables, then you most likely need to use the VLOOKUP function.
      Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

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

  16. Hi, I want to write a function to change yes/no responses to numbers; So I have yes/no answers to questions and want to do some basic stats (counts etc.) and need to convert the words into numbers (e.g. 0, 1 or 2). Can you please assist?

    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. Do you want to replace text with numbers? This is only possible with a VBA macro. You can set a value in another cell using a formula. What text does the number 0 correspond to? Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  17. Hi,
    I am trying to update a cell with 'Requested', 'Received' or 'Past due' based on 3 other cells with dates in (date requested, due date and date received). I can master 2 statuses but not 3! Please can you help. If there is a date in the 'date received' column this should supersede all other statuses.

  18. Iuse this function
    =IF(C150<DATE(2004,10,1),"250.00","0.00")
    so i need to add every date
    =date(2004,08,01)
    how can i easy to do this.

  19. Hi,

    I am trying a condition using IF formula, but even the blank cell is counted as value and get the output as "RED" for all the blank cells.
    My condition is "GREEN" if the score is 60 or above, "AMBER" if the score is between 50 & 59,"RED" if the score is less then 50.

    I tried the below formula; =IF(AND(BO2>=50,BO2<=59),"AMBER",IF(BO2=60,"GREEN",))))

  20. I am trying to find the formula for the following:
    A. B. C
    1. 0. A. D
    2. 1. C. A

    =IF(A1<1, C1),IF(A1=1, B1)
    I am trying to get the cell value if the cell A1 is equal to 1 display cell B1 if value is 0 display cell C1

  21. Hi there, I am doing a survey regarding customer satisfaction level to my restaurant. So there are three satisfaction level: A, B, C. I want to find out the problem that leads customer to grade C to our services/foods with precise date. But however I still couldnt make it. Could you please kindly help me out?
    The scenario is as below:
    Customer Satisfaction Level : A, B, C
    Date: 13-08-2020
    If "Customer Satisfaction Level:B" , so it should come out with value CSL:B13082020
    I tried with IFS formula and it is as below:
    =IFS(A2="Customer Satisfaction Level:A", "CSL:A",A2="Customer Satisfaction Level:B","CSL:B",A2="Customer Satisfaction Level:C","CSL:C")
    But however, one key problem is how should I key in DATE formula to the value_if_TRUE? Maybe any other formula that can help me on it ?

    Your help is very much appreciated.

  22. Thanks James. That did what I am looking for. Much appreciated
    Stuart

  23. hi there, i'm trying to return a value if a date is between two dates, basically trying to sort my data into quarters, so next to my date column i would like a quarter column and want this column formula driven based on a table of dates. is this possible? thank you.

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

  25. Hi,

    I am working on a table for some stats however I am wondering if there is a shorter way using an IF function to return the original value if true.

    For Example currently I use.
    =IF(SUMIFS(Forming_Bends_Data!$F$3:$F$50,Forming_Bends_Data!$A$3:$A$50,Controls!$A58,Forming_Bends_Data!$B$3:$B$50,">="&$C$24,Forming_Bends_Data!$B$3:$B$50,"="&$C$24,Forming_Bends_Data!$B$3:$B$50,"<="&$D$24))

    Im wondering if it is necessary to copy out the original string as these may need to be added to and are getting quite long.

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

  27. helo,
    if i have multiple figures expected in acell and want to test then, how can i do it?
    example in column a, i type 1, 4, 5 9, 17, 23,and 29 an others. i want to be informed in column B that if in A there is 4, it documents y, if 5, its also y and you find that they are around 2000 figures to be tested by y. how can i set it?
    many thanks

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

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

  30. Hi,
    I am trying to return the value of a cell if another cell is greater than an amount - it's a simple one but I cannot figure it out, can you help

    1. Hello!
      Without seeing your data it is impossible to give you advice.
      You may find this formula useful

      =IF(A1>B1,C1,"")

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

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

  33. Question. Im trying to figure out a formula for my scheduling.
    I put the cell as AM/PM/wholeday

    AM value is 5
    Pm value is 5
    Wholeday value is 10
    What formula should i use total their hours from monday to sunday. Thank you

    1. Hello Remy!
      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. 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. Thank you.

  34. OK, I MADE A HEAD WAY AROUND IT, I APPRETIATE SIR.
    THIS SYNTAX BELOW FINALLY WORKED FOR ME:
    =IFERROR(IF(J26="ON","ENUGU DISCO",IF(J26="FT","ENUGU DISCO",IF(J26="LS","ENUGU DISCO",IF(J26="PG","ENUGU DISCO",IF(J26="TP","TSP",IF(J26="IT","ENUGU DISCO",IF(J26="ET","TSP"))))))))

    ACTUALLY, THE FORMULA IS TO HELP ME DIFFERENTIATE REVENUE LOSES CAUSED BY DIFFERENT FACTORS IN AN ELECTRICITY DISTRIBUTION COMPANY I AM WORKING FOR.
    I REALLY APPRECIATE YOUR GUIDANCE OVER HERE.
    MORE GRACE TO YOU SIR.

  35. please I have a challenge using multiple IFS formula when dealing with strings, it returns parse error or #error. the example below:
    =IFS(J4>="ON","NOT APPLICABLE",[(J4>="FT","ENUGU DISCO")],[(J4>="LS","ENUGU DISCO")],[(J4>="PG","ENUGU DISCO")],[(J4>="TP","TSP")])

    1. Unfortunately, without seeing your data it is hard to give you advice.
      Remove all extra brackets.

      =IFS(J4>="ON","NOT APPLICABLE",J4>="FT","ENUGU DISCO", J4>="LS","ENUGU DISCO",J4>="PG", "ENUGU DISCO",J4>="TP","TSP")

      Maybe it will help

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

  37. 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! :)

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

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

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

  41. Hi. I want to tag a certain person as "regular" or "probationary" based on the XX number of their tenure derived from a formula and formatted to " X yr, X mo". My condition is that if the tenure is equal or greater than "0 yr, 6 mo" it should be tagged as regular. My problem is that it returns "probationary" to other values that start with "0 yr" even if the "X mo" is equal or greater than 6 mo.

    Hope you could suggest a better formula. Thank you in advance.

    1. Hello Mods!
      The information presented to you is not enough to give you advice. What format is “X yr, X mo” written in? Text or date? Please specify 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. Thank you.

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

  43. Hi. I am generating a document that:
    A1 B1 C1
    A2 B2 C2
    A3 B3 C3
    A4 B4 C4
    Column A has dates, Column B is auto populated from C1 on the today date, but at the end pf the day C1 resets and on next day B2 gets auto populated.

    Can anyone help?

    1. Hello Aurel!
      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.
      What does the phrase e mean "Column B is auto populated from C1 on the today date"?
      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. Thank you.

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

  45. Seeking help of below mentioned conditions.
    If I type greater than 10 but less than 16 questions in Phy/Chem/Maths in 30 minutes then I will be getting 5 points.
    If I type greater than 5 but less than 10 questions in Phy/Chem/Maths in 20 minutes then I will be getting 3 points.
    If I type greater than 2 but less than 5 questions in Phy/Chem/Maths in 10 minutes then I will be getting 0 points.
    If I type greater than 25 but less than 35 questions in Bio/Eng in 1 Hr then I will be getting 5 points.
    If I type greater than 15 but less than 25 questions in Bio/Eng in 1 Hr then I will be getting 3 points.
    If I type greater than 5 but less than 10 questions in Bio/Eng in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance. Thank You - Amit Sharma

  46. Seeking help of below mentioned conditions.
    If I type >10 but 5 but 2 but 25 but 15 but 5 but <10 questions in Bio/Eng in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance. Thank You - Amit Sharma

  47. I am not able to figure out while applying multiple If conditions. Seeking you help.

    Conditions-1 :
    If I type >10 but 5 but 2 but 25 but 15 but 5 but <10 questions in Biology/English/ in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance.
    Thank you
    Amit Sharma

  48. Hello there!
    Here is what I am trying to do:
    If text in Column D (from Workbook 1) matches text in Column E (from Workbook 2) then enter text from Column B (from Workbook 2) in Column F in Workbook 1.
    I cannot figure out how to use the vlookup function for this exercise...
    Help would be very much appreciated... thanks a lot.

    1. Hello!
      In your case, you cannot use the VLOOKUP function. When a search occurs in one data range, and you need to return data from another range, use the INDEX + MATCH functions. Read more about it here.
      If you use Office365, pay attention to the XLOOKUP function

  49. i need a formula that i want to equal first column numbers to second column texts for example firs colum 25 34 56 second column love like hate 25=love 34=like 56=hate and excel learn these equalities and i want to use them after for ex. text sheet 25034 25789 25678 34567 34897 56987 excel and i use left operation and next column 25 25 34 34 56(think that it is a column) and excel write ney column love love love like like hate

    if i use matlab it gets easier or struggle or should i use only excel
    please teach this trick
    (sorry for my bad english)

  50. Good day,

    I need to calculate data into a worksheet from specific Columns on a Mastersheet where it links back to specific Unit (Column B:B), amount of Sessions (Column D:D), but then also just for specific month (Column I:I).

    I could only manage thus far: =SUMIF(Master!B:B,CEN!B6,Master!D:D), but then how do I just get it to consider data for a specific month (Column I:I)?

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