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

  1. Hy I Make A Inventory Sheet And I Want To Highlight That Row Where Quantity is 0.

  2. how to get same value if its more than the actual value.
    for eg:- actual value is 100 and other value comes at 110 so i want 110 should display as 100.

  3. Hi, Could you please help me? I need a function. B6 to B8 each have a drop down list and in C6 I need either "Section booked" if any of B6, B7 or B8 has any text. If all blank then "Select"
    It has been driving me crazy. Thank you so much

    1. Hi Barbora,
      Please see below.
      =IF(OR(B6"",B7"",B8""),"SECTION BOOKED","SELECT")

      1. Hi Barbora,
        Replace "greaterthanlessthan" with the operators
        =IF(OR(B6 greaterthanlessthan "",B7 greaterthanlessthan "",B8 greaterthanlessthan ""),"SECTION BOOKED","SELECT")

        1. Replace greater than with

  4. Hi Steven,
    =SUM(G6:G100)+SUM(F6:F90)-SUM(E6:E90)
    I'm not sure if that is what you mean.

  5. Need some help please... I know I am probable over thinking this...
    so I am running three columns of numbers say , E,F,G.
    Column E is a Negative and Subrtacts From G
    Column F is a Positive and Adds to Column G
    But I would like a running total in C4, I was thinking that I could use the "IF" to read the last filled in cell in column G. so for example

    Cell C4 will show the running total amount without having to scroll down to G47...
    data starts in G6 and ends in G90 and with each entry it moves down to the next cell which is blank.
    I tried using the "lookup" to show me the value of the cell above the first blank cell but cant figure it out either...

    Geez I hope I explained this right.... compare it to a checkbook....
    Column E are withdrawls and deduct from G
    Column F are Deposits and Add to the current total in G
    I have all of the formulas for G and keeping the total but would like a "quick" glance to see the running total....

  6. Excel 2017 Have 4 columns, need an if statement to add dollar amount in the 4th column. Three columns are designated as A, B, & C, 4th is Amount. If I put an X in Column A , needs to show $10 in column 4, if i put an x in column B, needs to show $5 in column 4, if I put an X in column C, needs to show $1 in column 4.
    The first one I can get to work, not the others. In column 4 I put this in:
    =IF(A1="X",10,"")

    1. Hi Harold,
      =IF(A1="X",10,IF(B1="X",5,IF(C1="X",1,""))

      Is that what you're looking for?

  7. Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)-C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
    =IF(D7=D6,IF(B7="OFF",0)-C7) . Thanks

    1. SOLVED - =IF(D6=D6,IF(B6="OFF",0,B6)-C6) - Thanks anyway.

  8. Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)-C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
    =IF(D7=D6,IF(B7"OFF",0)-C7) . Thanks

  9. Can anyone help

    =IF(A4="Month"&B4="US:W2 Salaried",173.33)

    why it is returning false statement

    1. Hi Ankur,

      =IF(AND(A4="Month",B4="US:W2 Salaried"),173.33,"")

      Was that what you were trying to do?

  10. Could you please help, I want to write if E5 is greater or smaller than 0 enter that amount in C6

    1. Hi Chris,
      The formula below should work.
      =IF(OR(E5>0,E5<0,),E5,"")

  11. Hi,

    For exm, We are receiving any project 12345 between 6:30:00 AM to 10:30:00 PM then time should automatically 6:30:00 AM.

  12. Hello,
    Need one help. I have two values in cell A (Y or N) and i want to use IF statement but i am getting error. The statement is like this if the cell A contains N then it should return N else it should leave blank
    IF(A=N,"N","")

    1. Hi Madhosh,

      Be sure to always enclose text values in quotations marks and use appropriate cell references. For example:
      =IF(A2="N","N", "")

      This formula goes to any empty cell in row 2, and then you can drag it down to as many cells as needed.

  13. Need help.. is this possible?
    If value of A1 is item no. And value of A2 is the qty (i.e. 3 pcs), can A1 be copied in column C thrice? A1 = C1 = C2 = C3.

  14. =IF((T17+U17)-V84V84,Z17,0))

  15. Hi!

    I would to ask for your help with this formula.
    =IF((T17+U17)-V84V84,Z17,0))
    I got the same result which is 00:00 even if it has higher or lower value.

    Thanks!

  16. Hi Svetlana,

    I would like to return a value of "No" in B1 if A1 is either "0" or "na".
    Is this possible?

    Thanks!

    1. Hi Brett,

      Sure. Please use this formula for B1:

      =IF(A1="", "", IF(OR(A1=0, A1="na"), "No", ""))

  17. Hello, how do I make an if statement see the text in a cell and not a reference? In Cell F2 the value is "T19" I want to say, if F2 = T19 than "A19" with A19 being text not a reference.

    1. Hi Lee,
      =IF(F2="T19","A19","")
      Is that what you meant?

  18. It seems the IF(AND( function is breaking the formula.
    =IF(G5>10,2,
    IF(AND[G50],1,
    IF(G5=0,0,"")))

  19. =IF(G5>10,2,IF(AND ( G5 0),1,IF(G5=0,0,"")))

    The website seems to break the formula... I tried adding spaces to see if it will work...

  20. HI, May u help me with this situation?i cant get the right formula for that 0 or negative figure is equal to 0
    0 or negative =0
    >0 -10% = 1
    >10% = 2

    1. Sorry, guys, our blog engine sometimes breaks formulas in ">" and "<" symbols.
      If my understanding of the task is correct, this formula should work a treat:
      =IF(G5>10, 2, IF(G5>0, 1, 0))

    2. i have this as my current =IF(G5>10,"2",IF(G5<=10,"1",IF(G5<0,)))

      1. =IF(G5>10,2,IF(AND(G50),1,IF(G5=0,0,"")))

        Sorry, the original formula is broken, not sure why it did not paste properly.

      2. =IF(G7>10,2,IF(AND(G70),1,IF(G7=0,0,"")))

        Hope that helps.

  21. what is the formula for interval,
    fill the cell outstanding if score 4.5 to 5
    fill the cell V.Good if the score between 3.5 to 4.4, and so on..
    i tried to make rounding but it doesn't work if the number not whole even after rounding

    1. This one should work. You can change the text results to whichever you think fits better.

      =IF(E5>=4.5,"OUTSTANDING",IF(AND(E5>=3.5,E5=2.5,E5=1.5,E5<2.5),"SATISFACTORY",IF(E5<1.5,"FAIL","")))))

  22. Hi
    I have forgotten how to repeat a certain set of numbers for a spreadsheet.
    For example i have a list of 3 items and i want the IF function to repeat as 123123 etc.
    I have put 3 in the A1 cell and the number 1 in A2.
    Thanks

  23. Id like to ask how to use if function or whatever easy function can be used should I want to display the percentage in a cell if a condition has been met. The condition is if all specified range of cells contains any values like a word or date. Lets just say that cell A1 will display the percentage of completion if cells ranging from A2,A3,A4,A5,A6 contains a value like yes or no or a date.

    1. Hi Jason,

      It's not clear how you calculate the percentage of completion, but the idea is to count non-empty cells in the range A2:A6, and if the count is equal to 6, display the percentage, otherwise return an empty string:

      =IF(COUNTIF(A2:A6, "<>"&"")=5, percentage formula, "")

  24. Please i need you assistance to building formula as follows:
    if A1 contains specific text, A OR B OR C = CLOSED
    if A1 contains specific text, C = OPEN
    Many Thanks.

  25. I have a spreadsheet, with multiple tabs I need to be able to Concatenate a text cell (A3) with a date cell(B3) on a report form, but be blank when the date cell (B3) is empty. this is what I have so Far.
    =IF(AND(NOT(ISBLANK('Super(No7)'!A3))*NOT(ISBLANK('Super(No7)'!B3))),CONCATENATE('Super(No7)'!A3&""&TEXT('Super(No7)'!B3,"DD/MM/YYYY"),"")).
    The above formula works until I add the &TEXT& bit then it returns FALSE. can anyone help please.

  26. I'm trying to get an if, then formula to generate a number in a cell if the adjacent cell is a certain letter (for GPA calculations), but i keep getting a value error message. Here is what I wrote: =IF(C3="A",4,IF(C3="b",3,IF(C3-"c",2,IF(C3="D",1,IF(C3="F",0)))))

    1. That formula works except for when the value is "C".

      Change the "-" into "=".

  27. Hi,
    I am trying to have the value of a cell(B2) in Sheet2 populate in Sheet1 cell(B3). I thought I could do ='Sheet2'!B2, but when there is no data in Sheet2 B2 the value populates as 0 in B3, I would like it to remain blank.

    1. =IF(Sheet2!B2=0,"",Sheet2!B2)
      That should work.

      1. Thank you! Works like a charm!

  28. Please help me build a formula as follows:
    if A1 contains A,B or D then it is CLOSED, if A1 contain C then it is OPEN
    thanks

    1. =IF(OR(A1="a",A1="b",A1="d"),"closed",IF(A1="c","open",""))

      Should work for your formula.

  29. if C1, C3 & C5 have "text", how to total them in C10?
    (There are approx. 20 cells with or without text that would report to C10.)
    Example: C1 = smith. C3 = . C5 = jones. In this case, I want C10 to show the number "2", allowing me to calculate off of that.
    Thanks in advance!

    1. Hi James,

      To count cells with text, you can use the COUNTIF function with "*" as the criteria. For example:
      =COUNTIF(C1:C9, "*")

  30. I want to make a one formula in Excel for:
    IF 825>the answerthe answer4001,*0.5

  31. Hi,
    I need an help to auto calculate the order quantity, if a particular cell is less than eg : 60.

  32. I want to use the IF function to test if one cell (say H5) is above zero, then insert a given cell's value (say the numerical value in C5), if the test is false, then insert another cell value (say the numerical value in E5).

    1. =If(H5>0,C5,E5)

  33. i have the following formula:
    =if(isblank(A1),"", if(D1<0, weeknum(A1,1),""))
    working with this formula:
    =if(isblank(J2),"", hlookup(max(#REF!),$R$1:$DD$89,row(J2),false))
    to calculate weeks of perfect attendance. However, it has errored out for anything after week 52. how would i fix this to get it to continue past week 52.

  34. Hi,

    I have the conditional formula on my J cell that read as follows: =IF(G3="F2F","1", "0"). G3 has a drop down menu to choose from and one of the options is F2F. How do I add all the ones at the end of my J cells?

    Thank you,

    Elida

    1. Do you want different values for the different drop down values in G3? Otherwise, use the formula below. This will give you a 1 if G3 has either F2F,F2F2, or F2F3 (replace with whichever is on your list)
      =IF(OR(G3="F2F",G3="F2F2",G3="F2F3"),1,0)

  35. Hi, I'm trying to write an if/then statement that will represent numerical values. For example, If L48 is greater than L47, then show the value inside L48. If not, show the value inside L47.

    I get it to do the yes, no feature, but I'm having trouble getting it to display the value inside of L48.

    1. =IF(L48>L47,L48,L47)

  36. I need to lock one column so the formula is displayed but do not want the rest of the spreadsheet formulas to show. How can you freeze one column display the formula?

    1. You can remove the "=" so it does not act as a formula.

  37. Hi is it possible to do an if formula when a cell is either finance or cash have it do another formula to equal commission?

  38. Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4)

    1. Hello,
      The formula below should do the job for you:

      =IF(E1="Yes", F2, IF(E1="No", F4, ""))

  39. Hey,
    Your tutorial is great. I am trying to get this nested formula but somehow its only picking up last logical test values. Can you please check & advise.

    =IF(B$4>B7<B$3, "Expiring within 2 Weeks", IF(B$3<B7B$2, "Expired", "Valid")))

    Regards

    1. Hello Syed,
      Here is the updated formula for you:

      =IF(AND(B$4 > B7, B7 < B$3), "Expiring within 2 Weeks", IF(AND(B$3 < B7, B7 > B$2), "Expired", "Valid"))

    2. What is the cell you're trying to find the value of? B7?

      I would trying using IF(AND and doing it that way
      Instead of =IF(B4>B7B3,B7<B4),"True","False")

      1. oops, for some reason it got cut off.

        Instead of =IF(B4>B7B3,B7<B4),"True","False")

        1. Not sure why it's cutting off so I'll try one more time. "=IF(AND(B7>B3,B7<B4),"True","False")"

  40. Plz help.....
    If i write a number greater then 100 in a cell mistakenly. I want Excel shows an error in that cell.
    For example
    I write 23 26 56 100 in cells and write mistakenly 105. So i want to show 105 in error.

    1. I would just highlight the whole column and do a Conditional Formatting, Highlight Cell Rules, Greater Than..., 100. Then if the cell is highlighted in red you'll know it's over 100.

  41. Please show me formula for the below. Thank you!
    IF A1 = ABC B1 = 1234; if A1 change to DEF then B1=5678; also if A1 change to GHI then B1=91110

    1. Hello Cindy,
      Please try to use the formula below to solve your task:

      =IF(A1="ABC", 1234, IF(A1="DEF", 5678, IF(A1="GHI", 91110, 0)))

      1. I Tried several times, but it doesn't work. Could you please check and advise again. Thank you!

        1. Thank you for replying, Cindy.

          Most likely it is the delimiter that was causing troubles. Please try this formula instead, it should work:
          =IF(A1=”ABC”, 1234, IF(A1=”DEF”, 5678, IF(A1=”GHI”, 91110, 0)))

          I've updated the formula in my previous comment as well. Sorry for the confusion.

          1. It works now. Thank you so much!

        2. Hi Cindy,

          Try using commas instead of semicolons to separate the arguments (that depends on which character is set as the List Separator in your Regional Settings):
          =IF(A1="ABC", 123456, IF(A1="DEF", 5678, IF(A1="GHI", 91110, "")))

    2. This will only work on 4 letters at a time. I'm assuming you meant ABC = 123 and not 1234. If not this wont work for you.

      =IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64))))

      1. Actually I need 6 digits (ABC=123456). So it doesn't work for me! Thanks for trying!

        1. Here is the 6 letter version. To add more numbers you just have to copy everything from IF to the second & and change the number after the A1.
          =IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64&""&IF(MID(A1,5,1)="", "", CODE(UPPER(MID(A1,5,1)))-64&""&IF(MID(A1,6,1)="", "", CODE(UPPER(MID(A1,6,1)))-64))))

  42. Assuming that A1 is 2006 and B1 is 2006
    =IF(A1=B1,"True","False") comes up True
    but
    =IF(A1="2006","True","False") comes up False.

    How do I resolve this within a formula since the 2006 is coming from a different formula.

    Thanks in advance.

  43. I would like to check a cell for currency format and if its in £ then multiply it by the $ factor to convert it to $s' and if its in $ then leave it in $s'.
    example: A1 is the £ to $ currency factor; A2 is the value in £ or $; A3 will be the value of A1 in $s.
    I have tried using the IF function, but it will not recognise A1 currency format! I would appreciate some help with this. Many thanks.

  44. Good Morning,

    I am trying an exercise in Excel, we have a sheet some details, however, in the first column A2, have some digit number in the same column digit will be sometime 7, 8, 10 and some case digit numbers with dots then TWO digits.

    I am looking for a formula that can help me to write text in the next column............

    Example : =IF(A2=(len)8,"NOBILIA",IF(A2=(len)7,"IMPULS",IF(A2=(len)10,"LINEA DÉCOR"))).

    Will you please help me with it.

    Sincerely yours
    Ikram Siddiqui

    1. =IF(LEN(A4)=7,"IMPULS",IF(LEN(A4)=8,"NOBILIA",IF(LEN(A4)=10,"LINEA DECOR","")))

  45. Hi Svetlana,
    I am trying to achieve in the formula to show the following:
    If the cell drop down is selected with "CB2" is standalone to show "local", if "CB2" & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
    How can I achieve this?
    TIA

    1. Hi Tia,
      There seems to be a problem with the IF(AND()) function. The comment truncates the formula for some reason.
      =IF(A1="CB2","LOCAL",IF(AND(A1="CB2",A1="CB3"),"LOCAL & FAR",IF(A1="CB3","FAR","")))

  46. IF Early Bird criteria - Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. - 12:00 p.m. (for lunch) or 6:45 p.m. - 7:30 p.m. (for dinner).
    Write a logical function to find out whether a customer is an early bird. (refer to the 'Early Bird' criteria stated above) In your function, you will have to reference Column E values (day of week) and Column F (time of order) to determine whether customers are early birds. Think about how you will reference the early bird criteria.
    If a customer is an early bird, the function should return "Early Bird". Otherwise, the function should return a blank value.

  47. Can you help me to accurately represent this formula please:
    =IF(L4>0612,"High Risk")

    1. Hello Ricardo,
      Please try to add quotes so that the formula will look like:

      =IF(L4>"0612", "High Risk", "")

  48. i using if condition formula it taking only 10 conditions is it only 10 conditions allowed

  49. Hi Sveetlana Cheusheva
    Is it possible that if I enter any text in column"P", "L" should show what is there in "K".
    =IF(NOT(ISBLANK(P7)),"0","=K7")
    =IF(ISBLANK(P7)),"=K7","0")

    1. Hi Ashwini,

      I think you've overthought it :) A simpler formula will work:
      =IF(P7<>"", K7, 0)

  50. Can you help me write a formula for the following situation plse.
    If A1-A2 is less than zero, then show the negative value. If not, then show zero.
    Tks

    2. Hi Jaime,

      Here you go:

      =IF(A1-A2<0, A1-A2, 0)

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