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

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

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

    PLEASE GIVE A FORMULA

    ND

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

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

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

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

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

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

    pl. help us with formula.

    Thanks,,

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

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

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

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

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

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

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

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

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

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

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

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

  20. Svetlana, Irina please help :)

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

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

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

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

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

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

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

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

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

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

  31. Hi i need Help

    A B C D E Results
    2 5 1 3 4 B
    2 1 0 0 0 A
    0 0 0 0 0 -
    0 5 1 0 6 E
    1 2 4 0 1 C

    How Can i use IF Function on Results

  32. Hi could you please assist me: I have text in cell A1 and in cell A3. In cells B1 and B3 I have numeric values. all these data I enter myself. Now I have a formula: =IF(B1>B3;A1;A3) which works perfectly, but I want this to say that when B1 is higher then B3 it must result B1 into the cell provided (D1) and it does, now my problem is that when B1 and B3 has 0, then it results cell B3 automatically to D1 and it should rather say TIE or DRAW or even just make 0 (zero)

    1. Hello,

      It looks like you need to add a condition to your formula:
      =IF(AND(B1=0,B3=0),"TIE", IF(B1>B3,A1,A3))

      Please note that you may need to replace commas with semicolons as these are the delimiters you use.

  33. I tried a lot to apply the formula
    =word()
    but I am never able to get the result to convert a number into words.
    where I find it used in some excel sheet working properly.
    thanks for helping me on this issue

  34. Hi, Phoenix

    this correct formula =IF(SUM(G27:P27)<=800,AI8,"")

    if its total (G27:P27) less then =800,it will taking Data cell "AI8"and greater then =800 it will showing cell empty

  35. Hello,

    Just want to ask how to work on this:
    IF (SUM(G27:P27)<=800, AI8, "")))

    I just want to know if this is correct. Thank you

  36. Hello,
    I have very strange question, I hope you will understand and answer.
    I want to apply Formula IF(C5>0,TRUE,FALSE) It's worked, Great. I understand.
    But in my data I have 0 values in Coloured Row. I want to get results also "True" or anything else, for Coloured 0 Cells. But as per above formula it is giving results "False".
    Please do you have any other formula or Logic to solve this case?
    My Focus on Coloured Cell.

    Looking forward to your reply.

    1. Hi,
      use this formula

      =IF(C5>=0,TRUE,FALSE)

  37. How I can Use IF formula for colour Cells?
    If I want results as per colour of cells.

    Thank You.

  38. Hello,
    I have very strange question, I hope you will understand and answer.
    I want to apply Formula IF(C5>0,TRUE,FALSE) It's worked, Great. I understand.
    But in my data I have 0 values in Coloured Row. I want to get results also "True" or anything else, for Coloured 0 Cells. But as per above formula it is giving results "False".
    Please do you have any other formula or Logic to solve this case?
    My Focus on Coloured Cell.

    Looking forward to your reply.

  39. Old Lotus 123 had a function @dateinfo That would return a day for the date referenced with a switch to determine the day. How to in excel?

    (K10) (L10)
    Thursday 03-Mar-16
    @DATEINFO(L10,2)

  40. Need help please,
    I would like to automate my simple payroll to calculate PAYE for different Gross payments.
    here are the tax brackets
    From To Tax Rate
    1 10,164 10%
    10,165 19,740 15%
    19,741 29,316 20%
    29,317 38,892 25%
    38,893 and above 30%

    thank you.

    1. Hi,
      you can use this formula

      =IF(A1>=38893,A1*30%,IF(A1>=29317,A1*25%,IF(A1>=19741,A1*20%,IF(A1>=10165,A1*15%,IF(A1>=1,A1*10%," ")))))

  41. I want to know how can i use the calculation :

    i have a 2 dates one is Expected and one is actual if me actual is grater then expected then the total value will calculate

    1. Hello Jeevan,

      Could you please clarify what you would like to get if the actual date is greater than the expected date?

  42. Hi,
    Please help,I want to try formula =IF(E7="value","E7*25.4","NA"), I want that if There is value then it will multiply by 25.4, & also if there is not value like some text then it will come NA, but it will not come so please help me

    1. Hi ANKUR BANSAL,

      Use this formula

      =IF(ISNUMBER(E7),E7*25.4,"NA")

  43. My formula: = IF(C:C = "SAAS-WFC-CONV", "Yes", "No).

    Column C HAS SAAS-WFC-CONV listed in the column but my formula is returning No.

    How do I correct this? Are the hyphens throwing it off?

    1. Hi,

      correct formula

      =IF(C:C="SAAS-WFC-CONV","Yes","No")

      1. STILL NOT WORKING

  44. hi

    Please help, I have downloaded raw data from different months, I need to check and separate the data according to the months it was processed in. If a specific job was processed in Jan I need to return the job reference number from that month regardless of cell its currently located in.

    1. Hi Evans,

      It is difficult to provide a solution without seeing the way your data are organized. Could you please send an example of what you have and of what you would like to get as a result? You can send a sample table to support@ablebits.com, please include a link to this blog post and your comment number.

  45. Hi S.Cheusheva
    I need your help, I just want to one cell contain text in other cell list i think formula =ISNUMBER(FIND(E47,B3:B991)) in this E47 is my targeted cell

  46. I wrote this formula =IF(OR(F2>=3,F2<=17),H34*E34,0) I am trying to get H34*E34 to calculate if F2 is between 3 and 17 and if not to put 0 in the cell. What did I do wrong?

    1. Hi,

      use this formula
      =IF(AND(F2>=3,F2<=17),H34*E34,"0")

  47. hello,

    i want make a formula in excel with the below data:

    if the value of cell A1 is bigger than the value of cell A2 then the cell will be red otherwise the cell will be blue.

    thank you

    1. Hello Dimitra,

      You need to create two conditional formatting rules for cell A1:
      1) Select the option "use a formula to determine which cells to format", enter the following formula:
      =A1>A2
      Choose to format the cell red.
      2) Create another rule with a different formula:
      =A1<A2
      Choose to format the cell blue.

      Please see the following blog post on conditional formatting in Excel:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting/

  48. I Need some assistance with a formula please. If cell A1>=1 and Cell B1=ok then Cell C1 needs to return yes, if either of values are not met then Cell C1 must return No

    1. Hi,

      u can use this formula in cell C1

      =IF(AND(A1>=1,B1="OK"),"YES","NO")

  49. hi, i want a formula to use for tax !
    i want to say if B43(value) >= W10(value) then use the value in R10
    and if B43(value) =< W10(value) then use the value in R9

    can someone assist ?

    regards
    Jakes

    1. Hi Jakes,

      You can also use the following formula:
      =IF(B43>=W10,$R$10,$R$9)

      If you want to change references to R9 and R10 as you copy the formula down the column, then convert them to relative:
      =IF(B43>=W10,R10,R9)

    2. Hi,
      u can use this formula
      =IF(B43>=W10,R10,IF(B43<=W10,R9,""))

  50. Hello again,

    I used your wonderful suggestion for a formula and it works, except for one part. Here's the formula I used: =IF(F14="Baby",3,IF(F14="Child",16,IF(F14="Teen",29,IF(F14="Young Adult",49,IF(F14="Adult",69,IF(F14="Elder",89))))+IF(F17="Child",13,IF(F17="Teen",13,IF(F17="Young Adult",20,IF(F17="Adult",20,IF(F17="Elder",20))))-F15)))

    It works for teen through elder, but not child or baby. Not sure why. And by not working anymore, it won't subtract the number from F15 and I can't figure out why.

    1. Hello Wolfy,

      Your formula should look the following way:
      =IF(F14="Baby",3,IF(F14="Child",16,IF(F14="Teen",29,IF(F14="Young Adult",49,IF(F14="Adult",69,IF(F14="Elder",89))))))+IF(F17="Child",13,IF(F17="Teen",13,IF(F17="Young Adult",20,IF(F17="Adult",20,IF(F17="Elder",20)))))-F15

      However, please note that the condition "Baby" is missing from cell F17.

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