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

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

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

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

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

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

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

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

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

    Thank You

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

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

    Thank You

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

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

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

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

  13. Hi all,

    I've spent hours trying but keep getting error.

    I need a formula for the target grade a child will get depending on a test result. However (and this is the bit I'm stuck on) the result depends on when they sit the test.

    These are my options of times: Y7 baseline, End of Y7, Y8 baseline, End of Y8, Y9 baseline, End of Y9, Y10 baseline, End of Y10, Y11 baseline.

    If they get a 1 in Y7 baseline their result is MTG 3. Whereas if they get a 2 it's MTG 4. However, if they got 2 in year 8 it's MTG U.

    Hope that makes sense.

    Assessment point Result
    Y7 baseline 1 2 3 4 5 6 7
    End Y7/baseline Y8 2 3 4 5 6 7 8
    End Y8/baseline Y9 3 4 5 6 7 8 9
    End Y9/baseline Y10 4 5 6 7 8 9 10
    End Y10/baseline Y11 5 6 7 8 9 10 11

    MTG 3 4 5 6 7 8 9

    I hope there is someone out there who can help me.

    Thanks so much Emma

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

  15. For the example on Excel IF formula examples for dates:
    - If the date is left blank, what would be the formula?

    I really hope someone can help me. Thanks! :)

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail? Give an example of the source data. What result do you want to get? Thank you!

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

  17. HELP!

    I'm trying to create a formula that will recognize a negative figure in an answer and if it is negative then add to it, if the answer is not a negative figure to leave the original answer

    =IF((AND(U24>N4,U24<0)),(U24+N4),(U24-N4))

    1. Hello!
      Sorry, but I don't understand how your formula and question are related. 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.

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

  19. Hi,

    Please help me to find out a formulae for below mentioned condition.

    if the value is (0-6) = need to calculate the value (number)*5%

    if the value is (7-10) = need to calculate the value as (number)*10%

    But the difficulty is if my value is 8 means first 6 want to calculate as 6*5% and remaining 2 will need to calculate as 2*10%.

    So I want the result as ((6*5%) + (2*10%))

    If any possible way to write a formulae for that. please guide me.

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please use the following formula

      =IF(A1=8,6*0.05+2*0.1,IF(AND(A1>0,A1<=6),A1*0.05,IF(AND(A1>=7,A1<=10),A1*0.1,"")))

      1. Hi Alexander,

        This is not I am expecting,

        0-6 = 5%
        7-12 = 10%
        13-18 = 15%
        19+ = 20%

        For example my count is 20 means first 6 calculate by 5%, 7-12 will be calculate 10%, 13-18 will be calculate 15% ten remaining 2 will calculate 20%.

        Below the detailed condition and Please help me find out this.
        Cnt Value Result Condition
        1 50 $2.50 Value*5%
        2 50 $2.50 Value*5%
        3 50 $2.50 Value*5%
        4 50 $2.50 Value*5%
        5 50 $2.50 Value*5%
        6 50 $2.50 Value*5%
        7 50 $5.00 Value*10%
        8 50 $5.00 Value*10%
        9 50 $5.00 Value*10%
        10 50 $5.00 Value*10%
        11 50 $5.00 Value*10%
        12 50 $5.00 Value*10%
        13 50 $7.50 Value*15%
        14 50 $7.50 Value*15%
        15 50 $7.50 Value*15%
        16 50 $7.50 Value*15%
        17 50 $7.50 Value*15%
        18 50 $7.50 Value*15%
        19 50 $10.00 Value*20%
        20 50 $10.00 Value*20%
        Total $110.00

        1. Hello!
          The formula I gave you is in line with what you wrote earlier. The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
          I recommend paying attention to a similar question.

  20. What I’m trying to express is a valid #3 Statement so that when #2 or #1 statement goes before it, it doesn’t give me an error in the last column that follows them all. The W column.

    So H&R column cells values must be calculated. At all times, which is H10*R10.

    When U&V column cells have values I have to add the all values, which is (H10*R10)+(U10*V10). (If U10 holds no value then V10 will be calculated to 0) which is my issue. I don’t want the 0 in the cell.

    Example 1 formula is column V5. When this formula is input, I see the 0. Example 3 formula is then placed into column W & the value is properly displayed.

    Example 2 formula is an alternative to Example 1 which also goes into column V5. When this formula is input, the 0 is removed in V5 as desired. However this formula clears out all values for column W, & Y which requires it to properly display & it doesn’t.

    Is there a way to nest the formula for Example 3 [H10*R10] always but ONLY when U10 & V10 have values will I use: (H10*R10)+U10*V10, so that I can use Example 2 as it is? I think it’s messing up because V10 can equal 0 & when I use a formula to not have it displayed it throws everything in all WYZ columns off. I hope this was clearer?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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