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

  1. --=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))-- done

  2. Please help,
    what am I doing wrong? the cell just returns what is in AT2 regardles of what is in C9
    =IF($C$9="1",IF($C$9="2",IF($C$9="3",IF($C$9="4",IF($C$9="5",IF($C$9="6",IF($C$9="6",IF($C$9="8",IF($C$9="9",IF($C$9="10",BL2,),BJ2),BH2),BF2),BD2),BB2),AZ2),AX2),AV2),AT2)
    Thanks.

    1. never mind fixed it
      =IF($C$9=0,"",IF($C$9=1,AT2,IF($C$9=2,AV2,IF($C$9=3,AX2,IF($C$9=4,AZ2,IF($C$9=5,BB2,IF($C$9=6,BD2,IF($C$9=7,BF2,IF($C$9=8,BH2,IF($C$9=9,BJ2,IF($C$9=10,BL2)))))))))))

  3. i have a formula to work out if stock is short over or equal how do it add the equal =if (c2>b2;"over";"short")

    1. ---=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))----

    2. Amended =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")

      1. -- =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ") --

    3. =IF(C2=B2,"EQUAL",IF(C2>B2,"OVER",IF(C2<B2,"SHORT","")

  4. Hello, can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-AL4)
    If there is data in AM6 I get the result I want. If MED, NWRK, CLL etc is entered in AK4 and there is no data in AM6 I get #value. I would like a blank cell. This was working when it was just"MED" and had not added the others.
    Thanks for your time.

    1. Hi, The concept you put up is conflicting, please narrate the job properly in order to provide you with the correct format.

  5. Hello,Can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-AL4)
    This formula does what I want when there is data in AM6 but if AM6 is blank it returns #Value. This happens only when MED, NWRK, RQO etc is typed in AK4. Why?
    Thanks in advance for your time.

  6. I need a function to determine if age greater than 23, and should return the text Yes if true and No if false.

    1. =IF(A1>23,"YES","NO")

  7. IF THIS CELL IS LESS THAN OR EQUAL TO 30 COPY THE VALUE OF THIS ROW,IF NOT,ZERO,

    1. Hi Ron,
      =IF(A1<=30,A1,0)
      The site may have issues showing the formula. If the above is broken, try the one below.
      =IF(OR(A1=30,A1<30),A1,0)

  8. Hi
    can you help me to find a value
    if A1,B1,C1,D1,E1 have values like that
    0, 0, 2, 0, 0
    How we found this value
    C1 value not specified its Formula based value
    Regards

  9. I am trying to calculate a commission for realtors here in BC. We have a tiered structure here which is as follows
    7% on the first 100k
    2.5% on the balance.

    This is then further broken to reflect
    3.22% on the first 100k and 1.15% on the balance (agent representing buyer)
    3.78% on the first 100k and 1.35% on the balance (agent representing seller)

    I want to be able to put a client name in a cell and following cell the type of transaction (buyer or seller) and have a cell trigger a calculation for the above commission level when the word seller or buyer appears in the cell.

  10. I am working on an inventory thing and all I want is for the drop list I have created for the cells to subtract 1 from the total in another cell.

    Example - If C3 is (I select an item from the drop down) then subtract 1 from V3.

    I just have a floor plan that I have layed out for deployed monitors and want to be able to select from the drop down a Samsung monitor for example and then it take 1 away from the total I have in another cell. Should be super simple however I can't get it work with the IF statement.

  11. Hi A, Perfecto! Works like a dream. Thank you very much.

  12. Hi Carrie,
    please try the formula below.
    =IF(COUNTIF(SoldDetails!C8:C21,"NO"),"INCOMPLETE","COMPLETE")

  13. Hello,
    I have 2 tabs in one spreadsheet. On tab 1, I want the cell to change to 'Complete', if the column range on tab 2 are all answered 'Yes'. Otherwise, I want it to say 'Incomplete'. The answers for column on tab 2 are either "Yes" or "No". If there is even one 'No' in that column, I want the column on tab 1 to pick it up.
    The following formula is what I've tried and it is not working, although I get no error.

    Where tab 1 is named "Sold' and tab 2 is named 'SoldDetails'. Thanks for any guidance. Perhaps I should be using CountA to determine cells that are either 'No' or 'NA', but any help is appreciated.
    =IF(SoldDetails!C8:C21="Yes","Complete", "Incomplete")

  14. Thank you works like a charm.

  15. Hello! I am having trouble writing a formula.
    I need the date(that is not final) in C22 to project 107 weeks out and then compare that new date to 12/31/2024 and pick the earlier of the two. This new date would automatically go to cell c32.

    I appreciate any help. Thank you

    1. Hi Mallorie,
      Please try the formula below.
      =IF(C21<C22,C21,C22)
      You might have to format the columns to be shown in the date format.

  16. If *100 is not there it works for stocks, with the * 100 it works for options.

  17. =IF(OR(ISBLANK(N9),OR(ISBLANK(P9),ISBLANK(O9))),,(N9*(P9-O9))*100) I want to add a function so that it will only multiply by 100 if Row L9 to L123 says Debit or Credit. so if it does not show debit or credit it will put the number instead.

    1. Hi Stephen,
      =IF(OR(L9="DEBIT",L9="CREDIT"),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above only checks per row. You get a value per row
      =IF(OR(COUNTIF(L9:L123,"DEBIT"),COUNTIF(L9:L123,"CREDIT")),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above checks the whole range of L9:L123 if it contains debit or credit.

  18. Hello
    I need a formula for if() i have 9 cells in a row for montly sales example 2 4 6 7 0 3 5 8 3 {yes}
    I want to use =if to see if there is a 0 in the row to say yes or no

    1. Hi L,
      Try to formula below.
      =IF(COUNTIF(A1:A10,0),"YES","NO")

  19. Hi there,

    I need a formula that says:
    If column M says 'Edinburgh - Leeds' then N will say 163 miles.
    Then if column M says 'Edinburgh - Manchester' then column N will says 175 miles.

    I have to do this for many different distances but they two are just an example.

  20. Hi,
    I am preparing a Excel LOG template for my document submissions and tracking. Documents submitted will have revisions & approvals based on comments from client. I want to prepare an excel LOG with functions to return Doc status whether it is Approved (A), Approved with Comments (B), Re-submit (C), Reject (D), Under Review (UR). I tried with some combinations of IF function & CHAR functions.

    CHAR function
    =CHAR(MIN(CODE(N12),CODE(V12),CODE(AD12),CODE(AL12),CODE(AT12)))

    CHAR Functions is working fine with one limitation, after getting status (C) for submitted document, while I re-submit and enter (UR) in Rev. status cell, the value is not returning. I guess with a combination of IF & CHAR function it should work.

    Please help me to solve this.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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