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

  1. I require a code for

    IF E4 = Closed then E4 fill colour will be Green if not it will be Red

    Wonder if you can help? also need it for whole column eg E4-E20?? same formula just whole column

    1. Hello Matty,

      You will need to add two conditional formatting rules: click on "Conditional formatting" icon on the Home tab, choose to "Use a formula to determine which cells to format" and enter =$E2="Closed", set the necessary fill color and click Ok. Then create a similar rule for Red color depending on the value you have in other cells.
      You can find a detailed description of conditional formatting in this blog post:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

  2. Are there any rules while using the word "TRUE" and "FALSE" in IF formula
    For example,
    =IF(A2="TRUE","A","B") - Even A2 is true, this formula is not working. It gives always "B". Why? Then I just remove brackets and just put the formula like =IF(A2=TRUE,"A","B"), it is working. Any concerns?

    1. Hi Magil,

      The "TRUE" in brackets is a string, not boolean value.
      The formula =IF(A2="TRUE","A","B") will return "A" if you have the "TRUE" as string in cell A2. Please try to type "TRUE" in cell A2 and change the cell format to Text.

  3. if(V11>3 & V11<5)then (ok), if not (not ok)
    any one can help :\

    1. =IF(AND(V11>3, V11<5),"OK","NOT OK")

  4. Why does this change what I write?

    1. Hi,

      use this formula
      =if(D2=3001,"3",""))

      1. =IF(AND(D2>1501,D2<3001=3001,3,""))

        1. Formula didn't copy right - here you go.
          =IF(AND(N2>1501,N2=3001,3,""))

          1. Our blog engine often distorts "<" and ">" symbols in formulas, sorry for this, but we just cannot fix it on our side.

            If my understanding of the task is correct, I'd suggest using nested If's:

            =IF(D2>3001, 3, IF(D2>1501, 2, ""))

            If D2 is greater than 1501 but less than 3001 it returns 2, if D2 is greater than 3001 it returns 3, an empty string otherwise.

  5. I want to make a formula to say if D2 1501 but less than 3001 then "2" and if D2>3001 then "3". I just can't seem to use IF correctly to make this work.

  6. I want to make a formula to say if D21501 but less then 3001 then "2" and if D2>3001 then "3". I just can't seem to use IF correctly to make this work.

  7. In G8, enter an IF formula to calculate the share price difference between 2/25/2016 and 2/26/2016 for each of the 30 companies if that company’s share price was not zero on 2/26/2016. In case of a zero price, the formula returns “N/A”. Autofill the cells below to G37 with the formula in G8.Im not sure how to write this formula.

    1. I'd need a bit more info to actually write up a formula, but sounds like you'll want to do a VLOOKUP

      1. Hello!
        In a situation #N/A may use the formula IFNA.
        eg: =IFNA(VLOOKUP("Seattle",$A$5:$B$10,0),"Not found")
        (IFNA tests the result of the VLOOKUP function. Because Seattle is not found in the lookup range, VLOOKUP returns the #N/A error value. IFNA returns the string "Not found" in the cell instead of the standard #N/A error value.)

  8. Hello ,

    I want your support for if condition formula ,,,, I do export from 2 plants into different destinations and I want that the formula to chose for the destination the plant from which it departure , fro exemple: lets say departure plants ,, (X1 and X 2 ) and destination as follow, i have rapa , rochling and stirling park from X1 and for X2 i have the rest.....) i want the formula to put x1 or x 2 depanding on the destination,

    thanks in advance

  9. Hi,
    I was wondering if you could help me with a formula that would label all orders between 8:00 and 17:00 "Day" and all orders outside this timeframe "Night".
    I had =IF(H2>=8,H2<=17,"Day","Night" but it doesn't work.
    Thanks

    1. Hi Clara,

      use this formula

      =if(and(H2>=8,H2<=17),"Day","Night")

  10. Good day,

    Can you help with this formula: If C2=1 then D2=E1

    A B C D E

    1 (%) Spent to date 0.00%

    2 Feb-16 0 0.00% R -
    3 Mar-16 0 0.00% R -
    4 Apr-16 0 0.00% R -
    5 May-16 0 0.00% R -
    6 Jun-16 0 0.00% R -

  11. In sheet 1 Month & year and in sheet 2 period ( from to )i want formula if the ending date is 15th, month & year taken in sheet 1 earlier month & year. if date is 16th month & year taken in sheet 1 after month & year in sheet 1

    please help me.

  12. My if statement does not work on text data, only numeric. I have changed the format to read General or Text for the two columns that I'm trying to compare and it is as if it does not know it is text?

    1. Hello Vickie,

      Please make sure you enter text in quotes. If it still doesn't work, please share the formula you are trying to use.

  13. I want to enter an if function that says if something in cell A3 matches sheet 2 cells B2:B18 than insert what is in cell F2:f18.

  14. Hi,

    need help to formula the following:

    If A1 is ">40", A2 Value should be "1"
    If A1 is "31-40", A2 Value should be "2"
    If A1 is "30", A2 Value should be "3"
    If A1 is "20", A2 Value should be "4"
    If A1 is "<15", A2 Value should be "5"

    I can only get the 1st and the 2nd correct.
    results of 3rd-5th is the same as the 2nd which is "2"

    --------------

    I have another one which is perfect:
    =IF(Q13<6,1,IF(Q13<=6.4,2,IF(Q13=6.5,3,IF(Q137,5)))))

    but couldn't apply that same formula to the first problem.

    hope anyone can help me.

    Thanks!

    1. Hi Ali.
      You can use this formula:
      =IF(A1<15,5,IF(A1=20,4,IF(A1=30,3,IF(AND(A1>=31,A1<=40),2,IF(A1>40,1,"")))))

  15. I am having difficulty with a formula used to calculate whether a pupil has passed a unit in their course.

    This is the formula, but when I enter P in either of the first 2 columns then I get #NAME. Any help would be much appreciated.

    =IF(OR(BB7="p",BC7="p"),IF(or(BK7="p"BF7="p",IF(or(BL7="p",BG7="p",IF(or(BM7="p",BH="p")"P",""),""),""),"")

    1. Hi Janine,

      u try this

      =IF(OR(BB7="p",BC7="p"),"p",IF(or(BK7="p"BF7="p"),"p",IF(or(BL7="p",BG7="p"),"p",IF(or(BM7="p",BH="p"),"P",""))))

  16. Hi, this was very informative! Thank you so much!
    I'm wondering if you can take your idea one step further. I currently have =IF(F2>I2, "2", "0"), so if F2 is greater than I2, my cell shows a 2, and if it is smaller than, it shows a 0... but how do I include if F2=I2, the cell will show 1? (In essence, I'm trying to calculate scores for baseball, where a win = 2 points, a loss = 0 points and a tie = 1 point. Thanks in advance!
    Cheers,
    Shannon

    1. Thanks to your question I've managed to solve mine. Many thanks x

    2. Hi Shannon,

      =if(F2>I2,"2",if(F2<I2,"0",if(F2=I2,"1","")))

      1. Thank you, Hemanth! This is just the question that I had as well. Very helpful.

  17. Hi,
    am using =LEN(N4)-LEN(SUBSTITUTE(N4,",","")) to determine the number of text values separated by commas. I need to count the number of text values in the cell which are separated by a comma. e.g.
    cell b3 alpha,bravo, charlie
    cell b3 contains 3 text values, but only 2 commas
    How do we count the number of text values in a cell, or the number of commas + 1 ?

    in pseudo:
    if cell contains one comma,
    then comma count is one, but the text value is two,
    (if text values are separated by a comma, then this means that
    there is two text values)
    if there two commas found,
    then there must be 3 text values, etc.

    thanks!

    1. Hello Steve,

      Assuming there is always one text value more than there are commas, you can simply add 1 to your original formula:
      =LEN(N2)-LEN(SUBSTITUTE(N2,",",""))+1

  18. Hello,

    I have a spreadsheet that I need to remove duplicates but I have multiple rows have the same "Students Name" but the variances are on 2 columns Yes for Pell and Yes for DL. If the student received Pell and DL it shows in 2 rows I need to have it in 1 row. Also some of the students only received Pell but not the DL. I know I did this If formula last year but I just can't remember exactly please help.

    StudentName DL Pell FundSource
    Aguirre, Leticia Y N DIRUNSUB
    Aguirre, Leticia N Y PELL
    Aguirre, Leticia Y N DIRSUB

  19. I want to use excel formula using if. (If I enter 1,00,000 the result is to come 15,000 in specific column

    (If I enter 2,00,000 instead of 1 lac the result is to be come 16,000 in in the same specific column

    (If I enter 3,00,000 instead of 1 lac or 2 lacs & the result is to come 17,000 in the specific column

    (If I enter 4,00,000 instead of 1,2,or 3 lacs the result is to come 18,000 in the same specific column

    (If I enter 5,00,000 instead of 1,2,3 or 4 lacs the result is to come 19,000 in the same specific colum

  20. I need help to construct one formula
    i.e.

    if the value in A1 is greater than 10 than result will be 1 and if the value is less than 5 result will be .5

    1. Hi,
      =if(A1>=10,"1",if(A1<=5,"5",""))

  21. I had a formula in E12 as =Sum(E8:E11) simple right, but now I have to edit that so that the cells remain blank if no value has been entered for "Cash" in Cell 8. I have tried so many different combinations with IF and I am not getting this at all any suggestions?

  22. IF I TYPE W IN COLUMN A1 COLUMN B1 SHOULD DISPLAY 5

    PLEASE GIVE A FORMULA

    ND

    1. HI,
      =if(A1="w","5","")

  23. I seem to have a new one. My data has quotes already in the cell.

    Column A
    "Data1"
    "Data2"
    "Data3"

    Since it already has quotes, I am having trouble getting excel to recognize the data since the quotes are use twice?

    IF=(A1=""Data1"","No"

    Is there a way to say the data already has quote or to separate it in the formula?

  24. I am trying to to run a formula for keeping track of 2 cash floats. We have a main float and an overflow float. If we draw cash, I would like it to subtract from the overflow first, then when it runs out, draw from the main. The main needs to have a cap of $300, so when we add cash, it first adds to the main until it reached $300 and then will start adding to the overflow. Please let me know if you need any further information. Thank you for your time!

    1. Nevermind, figured it out. Thanks!

  25. hi guys can somebody help me about this function ?
    =IF(E2>=1000,"High Sale", IF(AND(E2=500), "Medium Sale", "Low Sale"))
    i'm pretty sure it's not true
    and also I can not understand what does it mean :

    Write a formula (using the data in All Data sheet) in cell B4, to display the total sales in state in cell A4. Your formula should be written in such away that it can be copied to cells B5:B15.

    1. Hello Arman,

      It looks like you need the following formula:
      =IF(E2>=1000, "High Sale", IF(E2>=500, "Medium Sale","Low Sale"))

      As for your second task, please describe it in more detail. We'll do our best to assist you.

  26. I need help with a formula that will say If W8 contains a date, then X8 needs to be that date plus 21 days. Example: If W8 has 3/12/2016 entered, I want X8 to show 4/2/2016 but ONLY if W8 has a date in it. Otherwise I want X8 to remain empty.
    I got this far =IF(ISBLANK($W8)=FALSE,"?????","") but don't know how to code the return of the "date + 21 days part" (the ????? part in the formula above).
    Thanks in advance!

    1. Hello Tonya,

      You can set the format of cell X8 to "Date" and enter the following formula:
      =IF(ISBLANK($W8)=FALSE,$W8+21,"")

  27. Hi..
    I want to replace all special character with space from a sentence in excel.

    pl. help us with formula.

    Thanks,,

  28. Hello everyone, I'm trying to populate a certain cell using this technique. What I got so far is this, but it doesnt seem to be doing what I want.

    =IF(E3<TODAY(), "=.5*D3", "")

    I want it so that when the current date is past the date listed in cell E3, the selected cell will populate half the value of cell D3 if true, and nothing if it is false. Unfortunately, I don't really know how to populate the cell with the equation if true.

    Can anyone help?

    1. Hello Vincent,

      Your formula is almost right,but it doesn't need the second equals sign and quotes as it gets the calculated value:
      =IF(E3<TODAY(), D3*0.5, "")

  29. I work at a school, and i manage the book shop over there, so i want to keep my record straight for my boss, i forget things so fast :) and also my purchase record, thats why i want to set a formula to fastly enter. i deal with notebooks, diaries and artpads, so I want a formula for text and numbers both, for example if i write 'D' in a cell, the formula will write the amount of '55' for a Diary, and so on for notebook, i mean if i write 'N' for notebook, the formula will write the amount '50' for me. I just want to set that formula. So i can deal with other customers also.

    1. Hello Ali,

      If your list of possible letters is limited, you can use several conditions in your formula:
      =IF(A2="D",55,IF(A2="N",50,IF(A2="A",45,"")))

      Here A2 is the cell where you enter the letter that corresponds to the product.

  30. Hello! I have worked out a formula that calculates hours from times given in order to calculate salaries. The following formula as you can see is for individuals that work 8hrs and sign in and out for their breaks.

    =IF((((AJ93-AI93)+(AL93-AK93)+(AN93-AM93)+(AP93-AO93))*24)>8,8,((AJ93-AI93)+(AL93-AK93)+(AN93-AM93)+(AP93-AO93))*24)

    This one calculates the O/T hrs above 8hrs

    =IF(((AJ93-AI93)+(AL93-AK93)+(AN93-AM93)+(AP93-AO93))*24>8, ((AJ93-AI93)+(AL93-AK93)+(AN93-AM93)+(AP93-AO93))*24-8,0)

    I am having two problems; the first is that I cannot work out how to do an "if" formula for an individual with only an "in" and "out" time?

    Also there is one whose "in" time is "pm" and the "out" time is "am" which is causing bigger problems. Is there a formula that doesn't need the dates? I have tried many "if" formulas but all are giving me a "-16" instead of an "8" no matter how I adjust it?

    This seems to be a very helpful forum.

    Any advice would be appreciated!

    Thank you!

    Kind regards.

    1. Hello Craig,

      You can add the following condition to process blank cells correctly:
      IF(OR(ISBLANK(AM95);ISBLANK(AN95));0;AN95-AM95)

      As for your second question, you can add one more condition to calculate time on different days:
      IF(OR(ISBLANK(AO95);ISBLANK(AP95));0;*IF(AP95-AO95>0;AP95-AO95;AP95-AO95+1)*)

  31. Hello, I am trying to create an equation that gives me a result such as, If They are part of an A List and also has a guest it adds it together, but if they don't have a guest it just adds them. If they are part of B List it adds them and if they have a guest it adds that as well. Trying to get the total of the Individuals and guests they are associated with for A list and then also for B list. (If that makes sense). Thank you!

    1. Hello,

      I'm sorry, but it is difficult to understand the task without seeing the data you have. Please send a sample spreadsheet to support@ablebits.com and include the way you want to see the result. Please add a link to your comment into your email.

  32. Want to build if formula

    =IF(ISNUMBER(SEARCH("1001",AH2)), "Clarification", "Yes")

    its working fine but want to add some more criteria to it.

    Eg: 1001,2001,3001, "Clarification"

    And 1002,2002,3002, "Information"
    and 1003,2003,3003, "error"

    need help

    1. Hello Ismail,

      If I understand your task correctly, you can use the following formula:

      =IF(OR(AH2=1001,AH2=2001,AH2=3001),"Clarification",IF(OR(AH2=1002,AH2=2002,AH2=3002),"Information",IF(OR(AH2=1003,AH2=2003,AH2=3003),"error","Yes")))

  33. We will fix 10 % threshold for one material, we have some condition, which is the threshold is greater than or equal to 10 % = Issue, the same condition we have to apply for -10% threshold also. how i can.....?

  34. Hi

    I have hour data in a1 cell(8:30) i want if a1 less than 6:00 it returns yes otherwise no but if a1 is 0:00 in that case how to calculate.

    1. Hi Saumendra Barik,

      Time in Excel is stored as a fraction of a twenty-four-hour day, so if you multiply the cell value by 24, you will get the number of hours. Therefore you can use a simple condition:
      =IF(A1*24<6,"Yes","No")

      0:00 will be equal to 0 in this case.

  35. Hi,
    i have one problem,
    i have Five columns, first- A2, B2, C2, D2, and last E2.
    than i want, when (A2-B2), if A2 more than B2 than showing Result Amt. Column C2 and D2 Column is nill, but B2 more than A2 than Showing Result Amt.Column D2 and C2 Column is nill. and calculate interest only for C2 Column, D2 is not apply for Interest Calculation, Calculation is (10000*15%)/(365*20)).
    plz solve as soon as possible.

  36. Hi!

    I have read alot of guides about conditional formatting/if sentences containing dates, but I can't find the answer I'm looking for.

    I have to cells containing dates: "outdate" and "birthdate".
    I need a if-sentence that checkes whether the date in "outdate" is before january the year after the year specified in "lamdate"

    I hope this example can clear this mess up:

    lamdate outdate check
    01.04.2014 01.12.2014 TRUE
    01.04.2014 01.02.2015 FALSE
    01.04.2014 01.01.2015 TRUE

    Any idea how to do this?
    Thanks in advance.

    Greetings from Henriette.

    1. Hello Henriette,

      You can try the following formula:
      =EDATE(EDATE(A1,(MONTH(A1)*(-1)+1))-DAY(A1)+1,12)>=B1

      I hope this helps.

  37. I have this formula: =VLOOKUP($A$1,PP!$A$2:$BU$191,MATCH(A62,PP!$A$1:$BU$1,0),0)

    I need to have anything under $10.00 not pull over. I am only looking for amounts above this $10.00

  38. im trying to figure put this formula
    example
    when we put no. 4 the value on the next cell will become 75%
    help

  39. I am trying to work out bonuses paid on individual total sales figures if it is more than the average total sales of all salespersons that is:
    Cell I7 is the individual sales figure and B14 is the average total sales figure if the individual sales figures is more than 20% of the average sales figure then 0.5% becomes payable

    Please can you help with formula mine is not working out here

  40. Svetlana, Irina please help :)

  41. I have a row consists of 10 cells, each one is the mark for a test
    Test1, Test2, Test3, etc.

    Please I need your help as I am looking for a formula which subtract Last cell from the previous one. And then if the result is greater than 1 , add 1 to The total grade if equal to 0 then don't add anything, smaller than 0 then -1.
    Later the same cells should sum the total for example:
    1+1-1+0

    Thanks in advance for your help

  42. I have a raw consists of 10 cells, each one is the mark for a test, then I need the following please:
    Test1, Test2, Test3, etc.

    Please I need your help as I am looking for a formula which subtract Last cell from the previous one. And then if the result is greater than 1 , add 1 to The total grade if equal to 0 then don't add anything, smaller than 0 then -1.

    Appreciate any help

  43. I have data of pan card no & if pan card 4th letter is P,F,C then i want in the front of 4th character of pan like that
    P-non company dedcutee
    F-non company dedcutee
    C-Company Deductee

    pl suggest formuls

  44. Hi
    I am trying to create an table where one can only enter one value in a row of 4 cells i.e. if a value is entered in any cell the other 3 cells are zero or blank.
    Thanks for your help

  45. hi,
    i just want that if number is even then column become green else red,

    please help me

    1. Hi Vibhash,

      =IF(ISNUMBER(A1),"Green","Red")

  46. Hi,

    I need help......
    How to command the balance at 2/16/2016

    date in out bal
    2/15/2016 10
    2/16/2016 2 2

    Thanks in advance.

    1. date in out balance
      2/15 10
      2/16 2 2

  47. I use the following formula to determine representation in a fictional parliament:

    =IF(E31>999999999, E31/2000000, IF(E31>99999999, E31/1500000, IF(E31<100000000, E31/1000000)))

    However, if the value is less than 500,000 it will equal 0, but I want anything under 500,000 to equal 1. How do I do this and keep the above equation intact?

    1. Hello,

      You can add the following condition:
      =IF(E31>999999999, E31/2000000, IF(E31>99999999, E31/1500000, IF(E31>=1000000, E31/1000000,1)))

  48. =IF(A3="RED","STOP",(A3="YELLOW","READY",("RED","STOP" ENTER COLOR)))

    WHAT IS WRONG WITH THIS

    1. Hello Rah,

      It looks like your formula should look like this:
      =IF(A3="RED","STOP", IF(A3="YELLOW","READY", "ENTER COLOR"))

      Please note that when you want to include another condition, you need to start it with another IF statement. You also need to enter any text you want to see in a cell in quotes.

  49. Hi,
    I'm wanting my cell to look at the cell next to it and if there is a date in there to work out a calculation. If it's blank then it is blank as well. Is this possible?

    1. Hi Maggie,

      You can use the following formula for your task:
      =IF(AND(LEFT(CELL("format",C1),1)="D",CELL("type",C1)="v"),A2+A4, "")

      You need to replace C1 with the cell address you want to check and "A2+A4" with the necessary calculation.

  50. what formula use if have more than 5 input and need one output

    1. Hello Prabhu,

      Please describe your task in more detail. It would be great if you could give an example of what you have and what you want to get.

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