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 15. Total comments: 2999

  1. how do i indicate a value for a particular month:

    if month=January, above cell + 31 days else above cell + 30 days

    What is the syntax for this function ?

    1. Hello Dharmesh,

      You can use a formula similar to this:
      =IF(month(A2)=1, A1+31, A1+30)

  2. hi,
    i have characters in row "A" in drop down box.
    i just need to display value depending on different products in row "A"
    for different products i have different value for multiplication . how can i do that on conditional bassis. suppose if i choose product A1 then it should choose multiply by 2, if i choose product "B" it should be multiply by 3 and so on

    1. Hi!

      Assuming the dropbox is in cell A1, and the value to multiply in B1, the formula goes as follows:

      =IF(A1="a",B1*2, IF(A1="b", B1*3, IF(A1="c", B1*4, "")))

  3. I am trying to figure out how to use excel to figure my time sheets. I have a computer program that will send me a print out that looks like this, I can import in excel and it would look like this..
    Example:

    CARRIE EVANS - CLERK
    Punch In Punch Out Reported Tips Time (Hours)
    Thu, 10-20-16, 6:50 am Thu, 10-20-16, 2:03 pm 0 7.217
    Fri, 10-21-16, 6:50 am Fri, 10-21-16, 2:00 pm 0 7.167

    I need an excel spreadsheet that I can Import this into and figure to the 15 min increments. Punch In is one cell and Punch out is one cell. I wish it was day, date, time all in different cells, but that is not the way it imports. Can anyone help me with this. Thank you

    1. each cell looks like this
      Punch in
      Thu, 10-20-16, 6:50 am
      Punch Out
      Thu, 10-20-16, 2:03 pm
      Reported Tips Time (Hours)
      0 7.217

  4. Hi! can you help me with a formula? i have sheet wherein C1=B1 multiplied to A1 but i want C1 to be blank if B1 is blank and C1=B1 if A1= 0 or blank..

    thanks

    1. Hello Clark,

      Try this one:

      =IF(B1="", "", IF(OR(A1=0, A1=""), B1, ""))

  5. I am trying to do an if formula for a Y n that if it hits a yes it then goes on to do a sum... but I cant get it to work, am I using the wrong formula
    =IF(J7="Y",P7/5,IF(J7=n,"0"))

    Please be kind as I am a novice

    1. Hi Peej,

      You need to enclose all text strings in double quotes, but not numbers:

      =IF(J7="Y", P7/5, IF(J7="n", 0, ""))

  6. Hi,
    In Cell "AE2" I need to show always "URGENT" until CELL "V2" value is " " (Blank). when Cell "V2' goes Greater than 0, Cell "AE2" need to show the value of Cell "V2".
    Please some body help

    Thanks

    1. Hi!

      Try this formula:

      =IF(V2="", "URGENT", V2)

  7. Hello,

    I'm using the following formula:
    =IF(OR($J34="CM",$J34="PDC"),$M34,0)
    In the cell it show #name?. What is wrong with the formula.

    Regards,

    Jercon

    1. Hi Jercon,

      The problem is in smart quotes (”CM” and ”PDC”), which is often the case when copy/pasting formulas. Just retype the quotes directly in the formula bar, and your formula will start working properly.

  8. hi, can someone help me with this formula? I am making an if/or furmula showing that if the service years>3 or is a college graduate the column will be a yes. (only one condition needs to be met) I have:

    =IF(OR([@[College Graduate]]="Yes")([@[Service Years]]>3,"Yes","No")

    thanks.

    1. Hi Jordan,

      The correct syntax of the IF/OR formula is this:

      =IF(OR(condition 1, condition 2), value_if_true, value_if_false)

      In your case:

      =IF(OR([@[College Graduate]]="Yes", [@[Service Years]]>3),"Yes","No")

  9. I am trying to evaluate a date in the previous cell. If the date is before the 15th of the month I wan the evaluation cell to return the first of the month. If it is equal to or greater than the 16th of the month, I want the evaluation cell to return the 15 of that month. Examples:

    Cell A Date Cell B Returns
    10/8/16 10/1/16
    10/16/16 10/15/16
    11/3/16 11/1/16
    11/22/16 11/15/16

    Any thoughts or suggestions are really appreciated!!!

    Charles

    1. Hello Charles,

      Try the following formula, where A2 is the date in the "previous cell":

      =IF(DAY(A2)<16, DATE(YEAR(A2), MONTH(A2), 1), DATE(YEAR(A2), MONTH(A2), 15))

    2. Hi Charles,

      Here You Go,(Paste in B1)

      IF(DATE(YEAR(A1),MONTH(A1),DAY(A1))=DATE(YEAR(A1),MONTH(A1),"16"),DATE(YEAR(A1),MONTH(A1),"15")))

  10. This might sound simple but I'm not as bright as I'd like to think. if j48 = 9 then 10, if j48 = 10 then 9. please help

    1. Hi George,

      You can use a couple of nested if's like this:

      =IF(J48=9, 10, IF(J48=10, 9, ""))

  11. I want to say: if a certain cell equals a value and another cell equals a value then use this value

    Ps. above values are txt not numbers.

    what I got so far is IF((B19="General Trading", B20="One Payment"),"27,185","")

    but it isn't working.

    Please help me

    1. Hello Mahmoud,

      You have to use an AND statement, like this:
      =IF(AND(B19="General Trading", B20="One Payment"), "27,185", "")

  12. Hi I am a basic excel user, I am using excel 2016, my question is:

    I am using the formula =IF(X4030="",TODAY()-W4030) to get the result but if the cell W4030 is blank the result is some strange value, I want excel to display nothing if the cell W4030 is blank, how can i fix this. Please help. Many Thanks

    1. Hi Syed,

      Try this one:

      =IF(X4030<>"",TODAY()-W4030, "")

  13. Hi, Svetlana,

    I would appreciate it if you could help me with this

    I am trying to display contents of a table T1 if value of D2 is BAT

    in a Cell E2 =IF((D2="BAT"),T1,"")
    but it gives #value! error. How can I display the table T1?

    T1 table is made up of G7:I11 cells.

    Looking forward to hearing from you

    Thanks in advanced.

    Warm Regards,
    Vilas.

    1. Hi Vilas,

      The IF function cannot display the contents of an entire table in a single cell. You can write a formula to extract the data from the upper-left cell of your table, and then copy the formula to an empty range containing the same number of rows and columns as your table does to pull the values from other cells. For example: =IF(D2="BAT", T1, "") Where T1 is the left-most cell of the table.

  14. Hi,
    I want to to create an IF function where the true value will equal the text in a given cell from another sheet. Is it possible to set up?

    thanks

    1. Hello BETHAN,

      Sure, you can do that. For example:
      =IF(1=1, Sheet2!A1, "")

  15. Hi want to get
    if i entered yes in A1,then it should return value 5 in B1,and if i entered NO in A1,then it should return value 0 in B1, is it possible to impelement.Please help me

    1. Hi Anish,

      Here is the formula for B1:

      =IF(A1="yes", 5, IF(A1="no", 0, ""))

  16. I have a data set where i have to find if a date is between 1 March 2016 to August 2016) and if they are then yes, else No.

    1. Hello Kevin,

      You can use a formula similar to this:

      =IF(AND(A1>DATEVALUE("3/1/2016"), A1

  17. Is there a way to carry over the excess of two numbers to another cell? For example:
    A1=74
    A2=80
    I want to add these two numbers together but it cannot exceed 100. If it does exceed 100, the excess will go to A3.
    How can I do this?

    1. Hi John,

      check below...formula

      =IF(A1+A2>100,A1+A2-100,"A1, A2 sum is below to 100")

  18. Hello.
    I need simple logical formula, but not understand.
    It is a cell with a value of 1 or 2, and need to be a value corresponding to the name.
    Please help to understand how to do it.
    Thanks.

    1. =IF(D2=1,”JĀ”,IF(D2=2,”Nē”,”Varbūt”))
      This formula doesn't work :(

    2. =IF(B2=1,”JĀ”,IF(B2=2,”Nē”,”Varbūt”))
      Doesn't work :(

  19. Dear how i can write this condition please
    if total score =8 "high" if it is 6 to less than 8 "medium", if less than 6"low"
    thank you

    1. Hi Ahmad,

      You can use this formula with nested IF's:

      =IF(A1>=8, "high", IF(A1>=6, "medium", "low"))

  20. hello
    can i get a support please?
    I need a formula that :

    if there is date equal date,
    if there is another thing equal empty

    many thanks

    1. Hello Ghayss,

      Because in the internal Excel system, dates are stored as numbers, there is no function that can distinguish dates. The only solution that I can think of is using the ISNUMBER function within IF:

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

      The above formula will return the contents of A1 if there is a date or number in it, an empty string otherwise.

      1. it works !
        thanks Svetlana,,,you are Queen !!!

  21. Hi, I try to build a formula which states if a cell contains the text value of "acq Opp ID", then the result should display only the actual ID [(right(Q2,LEN(Q2)-12)], if the cell Q2 does not contain "acq opp ID", then if the result is to be "-" (empty).

    1. Hi KeBo,

      Because IF cannot recognize wildcard characters, you can use either SEARCH (case-insensitive) or FIND (case-sensitive) function:

      =IF(ISNUMBER(FIND("acq Opp ID",Q2)), RIGHT(Q2,LEN(Q2)-12), "-")

      The 3rd option is using COUNTIF with wildcards:

      =IF(COUNTIF(Q2,"*acq Opp ID*")>0, RIGHT(Q2,LEN(Q2)-12), "-")

      1. Thank you SO much ! I was kind of on the right track but overthought it :)

  22. Him

    I'm trying to do the following.

    Having input either S, W or N into a column. I want another column to auto fill as follows, When S = W. When W = S and when N = N.

    Can you help?

    Thanks

    1. Hi Ken,

      You can do this with the following formula:

      =IF(A1="s", "W", IF(A1="w", "S", IF(A1="n", "N", "")))

      1. Thanks Svetlana!

        Really appreciated :)

  23. Same query as asked by Bhimashankar. How can I use range in "IF" function.

  24. Hi I need a formula for following condition

    in a column (sr. A3 to A23 )I have to enter some numbers, each individual number should fall within a range of 5 to 50. A1 is 5 and A2 is 50.

    If the more than two values from the data are out of range, statement as "Not Acceptable" in cell A24 should appear. Otherwise "Acceptable"

    If nothing has entered in the column from A3 to A23, statement "Pending should appear in cell A24.

    1. Hi Bhimashnakar,

      Try this formula:

      =IF(COUNTIF(A3:A23, "")=ROWS(A3:A23), "Pending", IF(OR(COUNTIF(A3:A23, "<"&A1)>2, COUNTIF(A3:A23, ">"&A2)>2), "Not acceptable", "Acceptable"))

  25. I require a formula where if the cell is 20 or greater it add the number 20 and if the number is less than 20 it just add whatever number is in the cell. e.g cell F1 has 14 ten it should show 14 and if cell f2 has 23 then should show the number 20 something the formula at the end, but where it doesn't just show F1 is less than 20 but the actual number in that cell? = IF(F1>20,"20",IF(F1<20,"F1"))

    1. Hi Andre,

      In Excel formulas, you should never enclose numbers and cell references in quotation marks unless you want to turn them into text strings. As soon as you remove the double quotes, the formula will work just fine:
      = IF(F1>20, 20, F1)

  26. I need a formula that does the following:

    If(E13:E10<44,"0")...but if E13:E19 is over 44 the value would be E13 minus 8

    1. Hi Kim,

      Use this formula for row 13, and then copy it down to other rows:

      =IF(E13<44, 0, E13-8)

  27. help if the validation check. for example if A1 = USD , B1 >1 , A1 = MYR, B1 < 1 otherwise 1?

    1. Hi Jerome,
      Try this:

      =IF(A1="USD",B1>1,IF(A1="MYR",B<1,1))

      1. Hi thanks for this however the result is FALSE if use the above formula. can this be put into data validation ?

        1. Yes U put in data validation and let me know if u get any problem.

          1. IF(M15="USD",O15>1,IF(M15="MYR",O15<1,1)) Hi venugopla. this is what i put in data validation. the cell i input will be 015 but no validation check. ie any value can be inputted

  28. is there a possibility if I select a name from a drop down list then the datas of the selected list must be displayed in next column,
    e.g
    in B2 i've a drop down list of continents Asia, Australia, america, Africa and if i select Asia in drop down list B2 then countries of Asia must be showed in 'C' column and same goes to other continents.

    1. its possible but you should have a seperate data with a list with all countries. Best way is each continent on seperate sheet and then use indirect and address functions together some lookup and iserror to remove unwanted results, but this is a bit complicated to explain whitout actual data because the number of results varay depends on your choice which means that if there is some other stuff happening in the result display area you will need to give more info.

      If you know how to use Pivot Tables thats the really easy way just put the continent into the fillter area the problem is that if you want to use that data afterwards you need to copy it.

  29. if

    A B
    Tom Mia Obc
    Kon Ali gen
    TOM ALI GEN
    KON MIA OBC
    broy ray sc

    what is the formula for column B

    b column fill to Mia =obc, ali = gen,ray =sc

    1. Hi Roky

      Note: Take old text in A1 column, need to add text in B column, see below formula..

      =REPLACE(A1,FIND(" ",A1)+1,4,B1)

  30. Hi!

    I'm trying to get a formula (either an IF function or Conditional Formatting function) to help me assign the proper Customer Name &/or Customer # to an invoice in the proper row, but it's difficult because there are numerous invoices for some customers and a few for others. Here is what the raw data looks like:

    Customer # Customer Name Invoice #
    123 ABC 123 - ABC <=PLEASE NOTE, not a real inv.#
    312456
    312376
    312789
    312654
    312333
    789 XYQW 789 - XYQW
    312421
    453 ZZZZZZ 453 - ZZZZZZ
    321367
    354754
    378987
    365214
    333999
    365409
    376232
    234265

    Thanks for your help!
    Jim

    1. give an example what is the data and what it should become and i can make you a formula because i dont really get what is happening in your data

  31. Hi
    i want to know how to get a value if

    if e2="rl5" then Return system date - 5 days
    or if e2="rl4" then return system date - 3 days

    thank you!

    1. Hi Timothy,

      You can use the following formula for your Return system date column:

      =IF(E2="rl5", "5 days", IF(E2="rl4", "3 days", ""))

  32. I need help to put formula like this

    If Column L from another sheet has 35 then then the value 4002 should be taken, if it has 56 then 4004, if it has 38 then 4102, if it has 39 then 4104

    Please help

    1. Hi sunny,

      U write formula in Sheet1, please put value (eg:35,56,38,39...)in Sheet2..
      Please try below..

      =IF(Sheet2!L1=35,4002,IF(Sheet2!L1=56,4004,IF(Sheet2!L1=38,4102,IF(Sheet2!L1=39,4104,"Not in Our Range"))))

  33. Hi,
    I am looking for a formula to add 21 days to a date if a certain phrase is in another column. For example - A2 is 01/08/2016, if B2 says 'PA1' I would like C2 to add 21 days to the date in A1. I have so far got =IF(ISNUMBER(SEARCH("PA1",B2)),"A2+21","") which works as far as getting C2 filled with an answer but I can't make the sum "A2+21" work as a formula - can anyone help?

    Thanks

    1. Sorry - Just read back - should read, A2 is 01/08/2016, if B2 says 'PA1' I would like C2 to add 21 days to the date in A2

  34. Hi Svetlana,

    Hope you can help me on this tough one and thank you in advance for any help you can give.

    So I have created an Excel spreadsheet which I'm tracking snow removal times that happen during the winter so I can charge my customers. Row 1, I have the times that have been summed up. Row 2 where I a value that been calculated from Row 1 to get number of times 15 mins fits in it (For example, a cell in Row 1 would show 1:20 minutes and I divide that by 15 minutes which should give me a value of 8. Now here's the problem, I been trying to use the "IF" function in Row 2 to perform a function say if the time in Row 1 is less than 15 minutes than the value should be 0 or if it's 15 or more than I divide that by 15.

    Any suggestion what I could do? Thank you in advance for any help you can give!

    Gary

    1. Hi Gary,

      I believe this formula does what you need:
      =IF(A$1*1440>=15, A$1*1440/15, 0)

  35. I want to use IF function, for a cell contains email address then the resulting cell will return with a numeric value as 1 and if there is written as "NA" then return a numeric 0 (zero), so that at last I can count by using sum function to add and get how many emails are there in my sheet. Please give me the solution.

    1. Hi Kamesh,

      You can use a formula similar to this:
      =IF(A1="NA", 0, IF(ISTEXT(A1), 1, ""))

  36. I want to write a function that will evaluate if a cell is >1, and if it is, copy the text in any designated cell and copy that text to another cell

    Thanks

    1. Hi Don,

      You can use a formula similar to this:
      =IF(A1>1, B1, "")

      Where A1 is the cell to evaluate, and B1 is the cell to copy the text from.

  37. Hi,
    Excel has always been my downfall. I need your help please.

    I need for D5 to show as either 0% or 25% data is from
    D4:D14 which would either contain Yes, No and blanks , Should any of the cells fro 4-14 contain a no then D5 should show as 0% and if 4-14 cells have a YES including the blanks then D5 should show as 25%.

    Your help is much appreciated.

    1. Hi Michele,

      Try this one:
      =IF(COUNTIF(D4:D14, "no")>0, 0%, 25%)

  38. Sir/Madam

    I wanted to know the IFCOUNT condition for bank use for example
    State Bank of India put the NEFT charges for other banks except SBI the criteria is

    UPTO AMOUNT CHARGES
    10000/- 2.87
    10001 100000/- 5.75
    100001 200000/- 17.25
    200000 ABOVE 28.75

    requesting you to help in this regard.

    Thanks & Regards
    Ibrahim Khan

    1. Hi Ibrahim,
      Please consider amount in A1 cell.. then follow below formula...

      =IF(A1>200000,A1*28.75,IF(A1>100000,A1*17.25,IF(A1>10000,A1*5.75,A1*2.87)))

  39. can someone help me how to compute

    if A5 is equal and not less than to 7083 then compute for (A5-7083)*.20 + 354.17. or if A5 is equal or not less than to 10,000 then compute for (A5-10,000)-.25+937.50

    1. I don't understand your question 354.17 "or" if A5...
      should be "And"?
      Try this:
      =IF(AND(A5>=7083,A5=10000,(A5-10000)-0.25+937.5,))

  40. Can someone help me please?
    I need a formula showing the following:
    =IF(B10>=50,"50",IF(B10<=51,"SUM(B10+B13)"))

    If the field B10 is greater than 50, then it should be 50, if the field B10 is less than 50, then it should be the sum of B10 + B13.
    But with the way the formula is written, if B10 is less then 50, then it showings SUM(B10+B13) in the field instead of the sum of B10 plus B13.

    1. You can change:
      =IF(B10>=50,"50",IF(B10<=51,SUM(B10+B13)))

  41. Hi, I have a problem with loop.
    The idea is that i enter a number at A1

    I want to know if the number
    ====================================
    less than of equal to 100 13

    101 to 500 7

    501 to 1000 7

    every 1 to 500 thereafter 6
    ====================================

    For example, i enter 450, the result is 13.
    550, the result is 13+7=20.

    I just know one step. "IF(A1<=500,13)"

    Please help.

    1. ====================================
      less than of equal to 100 = 13

      101 to 500 = 7

      501 to 1000 = 7

      every 1 to 500 thereafter = 6
      ====================================

  42. How to write the formula to count the no of Sundays beet ween two dates (ex-30/06/2016 beet ween 15/07/2016)

    1. Hi jayantha,

      You can use the following formula:

      =INT((WEEKDAY(A1-1)-A1+B1)/7)

      Where A1 is the start date and B1 is the end date.

  43. Hi I Wanted to Set Excel Formula For The Three Condition Like
    1.If The Date Fall Before 31.05.2015 The Rate Of Service Tax Will Be 14%
    2.If The Date Fall Before 14.11.2015 The Rate Of Service Tax Will Be 14.5%
    3.If The Date Fall After 14.11.2015 The Rate Of Service Tax Will Be 15% What Would Be The Formula For That Said Question

    1. r u get answer?

  44. Hi,

    Is there a way to display a value based on information across 2 cells. So for example I have 2 products with data detailing the month purchased so want the output to detail the product purchased or both. So the example below the value would come back under product bought for Cust 1 "Both", Cust 2 and 3 "Product 1" and Cust 4 "Product 2"

    Cust number Product bought Product 1 Product 2
    1 February April
    2 March None
    3 January None
    4 None August

    Thanks,

    Lee

    1. Hello, Lee,
      Please try the following formula: =IF(AND(ISBLANK(C2),ISBLANK(D2)),"None",IF(AND(ISBLANK(C2),NOT(ISBLANK(D2))),"Product 2",IF(AND(NOT(ISBLANK(D2)),ISBLANK(D2)),"Product 1","Both")))

  45. Hi
    I just want the if formula to return the same value that is in a particular cell, or make a caculation.
    it reads like this right now =IF(F3=" ",C3,((C3+F3)/2)).
    In case cell F3 is empty, i want to display the same value that is in cell c3. However, if f3 is not empty, I want to calculate the average)...
    right now it is just returning to me the result of average calculation ((C3+F3)/2) even if the f3 is empty...

    Can you help ?

    1. Hello Rob!

      In your formula, there is a space between the quotation marks =IF(F3=" ",...).
      Remove it and the formula will work fine: =IF(F3="",C3,((C3+F3)/2))

      1. Awesome !!!
        Thank you !

        How can I submit some more questions, but using the actual file/spreadsheet ?
        I have some Pivot Table related questions and i think it's hard to explain...

        Thank you Andrei for your help !

  46. Hello Svetlana,

    I have a seemingly simple formula, not sure why it doesn't work on me!

    =IF(ISNUMBER(SEARCH(OR("May-16",AX3),OR("Apr-16",AX3),OR("Mar-16",AX3))),1,0)

    I need a 0 or a 1 if the value in AX3 equals Mat-16, Apr-16 or Mar-16.

    This doesn't work either =IF(OR(AX3="Mar-16",AX3="Apr-16", AX3="May-16"),1,0)

    Please help!
    Many thanks in advance

    1. Hello Anon!

      You cannot compare a date with the text string "Apr-16".
      Use the following formula instead: =IF(AND(YEAR(AX3)=2016,OR(MONTH(AX3)=3,MONTH(AX3)=4,MONTH(AX3)=5,)), 1, 0)

  47. if i type A1 in cell B1 then i want the values in A1 in C1, please ans

    1. Hello Ajay,

      You can enter the following formula in C1:
      =INDIRECT(B1)

  48. Hello!

    Can you help me to create this formula:
    1 to 4 = LOW
    5 to 9 = MEDIUM
    10 to 14 = HIGH

    Thanks in advance :)

    1. Hello Roy!
      You can use this formula: =IF(AND(A1>0,A1<=4),"LOV",IF(AND(A1<=9, A1>4), "MEDIUM", IF(AND(A1<=14, A1>9),"HIGH", "out of range")))

  49. Hi! Can you please help me with this?
    For example: I want a cell to display "done" if material down and machine down was met and if not, it will display "attention".
    =IF((AND(Z3="Material Down","Machine Down")),"Done","ATTENTION")

    1. Hello Dev Reyes!

      In the AND statement, you forgot to specify the cell that should contain "Machine Down". For example:
      AND(Z3="Material Down",Y3="Machine Down")

      And the whole formula would be:
      =IF((AND(Z3="Material Down",Y3="Machine Down")),"Done","ATTENTION")

  50. I am trying to create a formula that if text = (specific text) then add 1, and the total number of (specific text) fields there are in that column.

    I have a specific ticket type for an event in column F in this spreadsheet, and I am trying to create a formula at the bottom that will tell how many of each ticket type there is.

    1. Hello Jeanette!
      To count the number of cells with a "specific text" you can use the COUNTIF function. For example:
      =COUNTIF(B1:B8,"specific text")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)