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

  1. Hi team,
    Why if functions excepts text greater than any number value? if I write values in A1 -100, A2-200, A3-300 and write function in B1 =if(A1>=250,"Fine",if(A1>=100,"Not so bad","")) when instead of 100 i write in cell A1 text NO VALUE - if function returns me "Fine"

    Thanks

    1. up,, any info about why text values are greater than number values in logical functions?

  2. I am trying to write that if there is a 1 in Column B2 then write "refer to page 34". I have been using =IF(B2=1, "refer to page 34"). This works great when there is a 1 in the reference column but when the column is blank I keep getting a False statement. Help please

    1. Hello, Dave,

      The point is that IF function uses 3 arguments. You specified what to return when the value is 1, but you didn't for when the value is not 1. If you need the cell to remain empty in that case, please try this formula:
      =IF(B2=1,"refer to page 34","")

      Hope this helps!

  3. Hello Ma'am Svetlana:

    Good day.

    Ma'am, I am working on a database using excel. In an if statement, how can I return the address of a particular cell if argument is true. The statement should go like this, as an example, "Please check entry in cell G26"

    The cell "G26" is the cell address being referred whose entry should be checked if argument is true. The formula will be repeated in the rows of the database therefore it is expected that when the formula is copied to the succeeding rows, excel should automatically adjust the row (the 26) corresponding to the row where it is pasted. This will spare me from editing the formula every time it is being copied into a new cell on another row. Is this possible?

    I managed to get a returned address of a cell but it doesn't automatically update once copied to another row and so need to keep on editing. Besides, if possible I wish to get an address which is relative (G26) not absolute ($G$26). Also, I wish that the column would be in alphabet, not number.

    Thanks in advance for any help I can get.

  4. Required formula for below condition
    Final result depend on 6 column status example as below
    If cell A1=2, and B1=2,C1=2,D1=2,E1=2,F1=2 result should be “U”
    If cell A1=1, and B1=2,C1=2,D1=2,E1=2,F1=2 result should be “V”
    If cell A1=1, and B1=1,C1=2,D1=2,E1=2,F1=2 result should be “W”
    If cell A1=1, and B1=1,C1=1,D1=2,E1=2,F1=2 result should be “X”
    If cell A1=1, and B1=1,C1=1,D1=2,E1=2,F1=2 result should be “Y”
    If cell A1=1, and B1=1,C1=1,D1=2,E1=2,F1=2 result should be “Z”
    There is so many permutation and combination but I need above combination in single formula

  5. The following vlookup function

    =(VLOOKUP($E12,'SPORTS'!$A$9:$G$162,6,FALSE))

    returns the value as "#N/A" because the data is not available in the "SPORTS" tab. How can I change the value to "0" from "#N/A"?

    Thanks

    1. =IFERROR(VLOOKUP($E12,'SPORTS'!$A$9:$G$162,6,FALSE), "0")

  6. Hello Team,

    "If value of Cell d4 between -5 to +5 then display "Correct" Else "Error".

    Kindly help me with a formula for the above condition.

    Thanks in advance.

    1. If you have Excel 2013 or higher, use the conditional formatting tool.

  7. Hi, I am trying to use multi IF function to show certain range of values. for the numbers I have got it right but when it comes to text it is not reading it. this is the formula:
    =IF(AS55<=(AS54*0.92),20,IF(AND(AS55=(AS54*0.92)),50,IF(AND(AS55(AS54)),80,IF(AS55>=(AS54*1.05),95,IF(AS55="s/d",330,331)))))

    The idea is to show cut-off values where eg.if the number I get in the cell is <92% shows 20. However, if it is just a text "S/D" then it should read =330. can you help please.

    Note: It is working for me as single IF function but in the combination it is not reading it!=IF(AS55="s/d",330,331)

  8. I want to achieve three outcomes from three arguments. I am attempting to compile a register containing current insurance policies. The policy dates are either current (OK), out of date or not provided at all.
    1. TODAY()
    2. Date registered (A4)
    2. OK (A5)
    3. Out of date (A6)
    4. Not submitted (A7)

    My attempt: =IF(TODAY()A4,"A6",IF(ISBLANK(A4),"A7")))

    This doesn't work and I suspect I don't understand functions well enough to achieve my desired outcome. Can you assist.

    1. Oops typo. Should be =IF(TODAY()>A4,"A6",IF(TODAY()<A4,"A5",IF(ISBLANK(A4),"A7")))

  9. Hi I need help in applying if function for a range of cells which contains percentage and text. Ex. A1 contains 100% and B1 contains NA; while I applied if function, if(A1=>100%,1,if(A1<100%,2,0)), which worked when cell values had percentage but gives wrong output if cell contains any text, gives 1 for NA as well. Kindly assist.

  10. Please help on this

    =if(A3=E1&B1=C1, D3*G3,0)

    SIMILAR ABOVE I WANT TO CREAT.

    1. =if(A3=E1&B1,C1, D3*G3)

  11. Hello Team,

    Can any one help with, Actually i want to find the things from 3 column,
    Eg i have 50 Servers list those i need to find from 1000 D column list which i can find by using =vlookup function but every servers having its status with retired, Decom, Active in H column, So i need to find if Server in D column status in H column with Retaied or Dcom then find as true.

    1. Hello Rajesh thanks for looking into it, i have drop an email with my concern. Hope that clear to you.

  12. How to write the IF function in case of a dubble letter column.
    For Example
    =IF(AA18="";"Close";"Open")
    I get a ?NAME error.

    1. =IF(AA18="","Close","Open")

  13. =IF(AND((FM22)>Q2,(FM22)<R2),1,0)

  14. Hello,

    I do not think any of the examples contain what I am after. I have Excel 2010 Pro Plus. I have a column of dates, I need a formula that will fill the empty cells with 01/01/1900 00:00:00 AM if they are empty, otherwise I need to keep the existing date. I do not seem to be able to find this any where.

    Could you help, please?

    Regards,
    Paul.

  15. Ok I need to look at 3 cell Column A, B, C. In D I figured out the first part IF(A1=B1,IF(B1=C1,A1,A1&"/"&C1),A1&"/"&C1). In E I figured out the second part IF(A1>B1,IF(B1>C1,A1&"/"&B1&"/"&C1,A1&"/"&C1),A1&"/"&C1). In F I tried to combine the two I have IF(A1=B1,IF(B1=C1,A1,IF(A1>B1,IF(B1>C1,A1&"/"&B1&"/"&C1,A1&"/"&B1&"/"&C1),A1&"/"&C1)),A1&"/"&C1). It works but not for all my number combination If A and B are the same or If B and C are the same it give me A B and C. I think I need a third part not sure. If A B and C are the same I want A. If A B and C are all different I want ABC. If AB or BC has the same number I want AC not ABC which is what I am getting.

  16. I need to check if a cell is blank then if another cell which is a date is past 30 days then change it to red

  17. Hi guys, Please assist with IF formula. 1. I wanna compare two columns and isolate all the numbers that are not duplicates into a separate column.
    2. I also wanna compare the isolated numbers to another column that has data.
    Example

    Column A *Column B Now the odd one out must go to Column C
    1234 1338 1338
    1245 1245
    1336 1336

    There after I would like to again see if column C data is not in another column, say Sheet 2 Column F

    your assistance will be highly appreciated

    1. Hi, Andy,

      for starters, if the data begins from A1, you can enter the following formula into C1:
      =IF(A1<>B1,B1,"")

      and copy it down the column to isolate all non-duplicates.
      Then you can use one of these ways (to your liking, depending on what result you want to see) to compare column C with another one.

  18. Hi

    Could you please help me to find a solution for the below , I have made table in excel sheet like below, and Ihave nade aut list in colum a (Cash,Card, Cheq) what I need from ecel is if I select cash mode from colum a the amount what Iam entering cloum b should come in colum c automotically if I select card , colum b amount should come in colum d ,if select cheq colum b amount should come in cloum c.
    which formula I want to apply in order to work this table ?
    A b C D C
    mode rent cash card chq

  19. Hi all,

    I'm not even sure how to create a IF function for the following .. PLEASE HELP!

    I am trying to create a IF function formula.. if someone can help me create.

    If between 1-3 years you get 10 days
    If between 3-6 years you get 15 days
    if between 7-9 years you get 17 days
    if 10 plus years you get 20 days

    After your 10th, every 5th year anniversary you get an extra 5 days for example 15th year you get 25 days, 20th year so on...

  20. How to create an if then statement that follows as such:

    If A5 = "Orange" AND B5 = "Banana" then populate in C5 "Fruitbasket" with yellow background

  21. I'm trying to do monthly columns from a previous column in same spreadsheet with random/multiple dates; pulling a dollar amount from ea month into it's own months' column. Is this possible?

  22. Hi,
    Could you help with the following that I want use in conjunction your Merge Two Tables Add In? I read through your "IF function in Excel" page and the comments but don't see an answer to my problem:

    I have the following headers for columns A to F
    Appointment Status, Session, Date, Consumed, No Show, Balance

    In column A I have 3 states: Complete, No Show, NoShow NoEmail
    In column B I have various possible text strings: e.g. "PhonePill Flexi 20" "PhonePill Flexi 30", "PhonePill Flexi 40" the last number represents minutes.
    In column D, I used this formula =RIGHT(B2,SEARCH("",B2,2)) to show that last number from the column B text string: 20, 30 or 40

    However, what I really want is for those numbers to go in the appropriate Consumed (D) or No Show (E) column: i.e. if the Appointment status =Complete the number should go in column D. If the Appointment Status= No Show or = NoShow NoEmail, the number should go in column E

    Thanks for your help.

  23. Hi , could some one help me to create a formula please
    i have two products alloy and carbon steel. these have composition in maximum and minimum range in percentage . if composition is different then given minimum and maximum range result of formula should say pass or fail . range is :
    Steel Type Carbon Manganese phosphorus sulphur nickel chromium
    Carbon Steel Max 2.1% 1.5% 0.5% 0.3% 0.25% 1.5%
    Min 0.5% 0% 0% 0% 0% 0%
    Alloy Steel Max 1.5% 5% 1% 1% 5% 15%
    min 0.5% 0% 0% 0% 2% 11%

  24. HOW TO USE IF FORMUALA IN CASE OF
    C=COMP
    P=IND
    OTHER ALFABATE IS =FIRM

  25. Good Day

    I have managed to ad the if formula no problem. thank you for that.
    How ever I would like the cell background to turn green if the result is favorable.

    regards

    Leanne

  26. Hello

    I want to create a list to automatically fill in my prices. I have two values to look at: 1. is the size of the item. 2. is the description of the item.

    Ex: 10 Straw
    So it looks up "10" which refers to a pricelist.
    Then it looks up "Straw" from the list it was just referred t by the number.
    It then should return a price that is in the list.

    Any ideas?
    Having a dropdown would be even better. So you can choose your size form a list, and then in the next cell choose the item that is in that list.

    Thank you in advance,

  27. I am trying to write a formula in E5 where if D5 is > $5,000.00 then subtract $5,000.00 from D5, if not then blank.

    Thanks,
    Larry

    1. Hi Larry,

      Here you go:
      =IF(D5>5000, D5-5000, "")

  28. Hi,

    I'm looking for a way to get an average from a set of values with "<" contingencies. For instance I need the average of cells A1:A6. If <10 is present I would like to use 5 instead. If there are "<" than I would like it to use the values given.

    thank you in advance
    -Esmeralda

  29. Hi.

    I have been googeling this for some time now, and cant find out how to:

    check if the cell value is less than another cell value, and if it is, add a cell value to the first cell.

    A1 = 120, A2 = 200, A3 = 300. I need some function to check if A1 is less than A2, which it is, and then add A3 to A1, so A1 becomes 120 + 300 = 420, and is no longer less than A2.

    if anyone have an answer for this, i'll be very thankful.

    thank you in advance.

    - Morten

    1. Hi, Morten,

      I'm afraid you won't be able to put the formula in A1 and reference A1 at the same time. You will have to enter the formula to some other cell, say A4, to return the result:
      =IF(A1<A2,A1+A3,"")
      Also, note that if A1 is not less than A2 the cell with the formula will remain empty.

  30. Cell A1 Data like this "10+10"
    i want the result in B1 like "20"
    which formula i want to use. anybody help?

  31. I am trying to figure out how to use an if function when a cell contains a date in a format such as 1/1/2004. What I am trying to do is strip the month and day information and have an if statement that will return a result of "2004". For example if 5/5/1998 it should return a result of "1998". Can anybody help?

    1. Hi,Soccer Guy

      use this formula if your data in A1 "=YEAR(A1)"

  32. Hi,

    If A1 cell value 1, B2 cell value 2 = i need result not more then value 2

    Do you help me? How to create formula in excel?

  33. Hi,

    I have a list of activities if I select any one of those activities in one cell then the other cell should reflect as Production. Could you please help me with the formula

  34. Hi I need help figuring out formula to do.
    =if cell is 1-3=0 if cell is 4-7=5 if cell is 8-11=10 if cell is 12-15=15. using signs.

    ex.=IF(D2>=4=5,IF(D2>=8=10,IF(D2>=12,15)))
    where d2 is number of disks used
    and working from G2 where it is discount given in currency

    0-3 disks= 0$
    4-7 disks= 5$
    8-11 disks= 10$
    12+ disks= 15$

    1. Hi, Michael,

      If I understand your task correctly, you can try this formula in G2 (make sure to set the Currency format to the cell):
      =IF(AND(D2>=0,D2<=3),0,(IF(AND(D2>=4,D2<=7),5,(IF(AND(D2>=8,D2<=11),10,(IF(D2>=12,15,"")))))))

      Hope it helps!

  35. Good day. Instead of doing double entry on an excel spreadsheet, how do I link the sheet to read the following:
    Spreadsheet 1, Column C has number 12345, but spreadsheet 2 replaces the 1 with 70999 in Column C, thus creating 709992345. What formula would I use?
    Thank you in advance for your time.

  36. I have an excel sheet with three columns.
    I want to create a nested if formula that shows the following
    =If(RR>8,"P1",If(RR<=3,"P3","P2"))
    How do I re-write this formula in a situation I do not want a value returned when any RR cell is empty?

    Thanks

    1. Hello, Peejay,

      If you reference RR1 cell, then
      =IF(RR1="","",IF(RR1>8,"P1",IF(RR1<=3,"P3","P2")))
      then copy the formula down the column to return the values for other RR cells.

      Also, note that the formula above will return the text values P1, P3, and P3.
      If you want those to be cell references, then use the following formula:
      =IF(RR1="","",IF(RR1>8,P1,IF(RR1<=3,P3,P2)))

      Hope this helps.

  37. I have an excel sheet with a single cell (Ctrl+;) showing 'TODAY'S DATE', and two columns:

    Today's Date: 24/04/2017

    NAME DATE JOINED WORK
    John Smith 24/04/2017
    Mary Jane 01/04/2017
    25/04/2017
    Paul Loyd 02/04/2017
    Robert Gray 29/03/2017
    25/04/2017
    Ann Buck 24/04/2017
    25/04/2017
    25/04/2017

    One column for NAMES of staff, and one column for DATE JOINED WORK.
    I would like to always have 'tomorrow's date' in all cells in the DATE JOINED WORK column. And, if a name is entered on the NAMES column, I would like 'today's date' to appear in the DATES cell.
    When a cell in the NAMES column is blank, the cell in the DATES JOINED WORK column should show 'tomorrow's date'

    How do I do that?

    Thanks!

  38. i want convert formula to values like below
    =sum(A1+B1+C1) A1=10,B1=10,C1=10 total result D1"30"
    i want convert the result to values but i cont.
    i tried all the way in formula bar its not showing 30 its showing formula only, after one time using formula its want to show only 30 in formula bar also

  39. i want convert formula to values like below
    =sum(A1+B1+C1) A1=10,B1=10,C1=10 total result D1"30"
    i want convert the result to values but i cont.
    i tried all the way in formula bar its not showing 30 its showing formula only, after one time using formula its want to show only 30 in formula bar also

  40. Hi,
    I need an excel formula for the following:
    IF A1 & B1 HAVE NUMBERS THEN ADD THOSE NUMBERS, IF B1 IS BLANK THEN RETURN A 0.
    I am working on a compensation spreadsheet containing market adjustments for some of the staff. I calculated the market adjustment and need to calculate the new base rate. If the base rate did not get an adjustment then I need the cell to show 0. If the base rate did get an adjustment I need it to add the current base rate with the calculated adjustment for the new base rate.
    Thank you in Advance

  41. I need urgent help. I need to copy an formula into an spreadsheet of mine. Using Vlookup & IF. I have 3 ranges to work with. Pink, Blue, yellow and the sheet im using has 3 columns but mine has 3 actual rows with 3 different codes. How will I do this?

  42. Hi
    I am trying to do a simple less than < formula but can't seem to get it to work. I have two columns with dates in them say A & B. I want to be able to say:

    if A1 is than A1 then B1. If A1 is blank I want it to input what is in B1.

    Any help would be great!
    Thanks

    1. It would be wrong to check if A1 is less than A1. But, in case you misspelled and wanted to see if, for example, A1<A2, and return B1 in both cases, try the following:
      =IF(A1<A2, B1, IF(ISBLANK, A1), B1, "")
      If it's not helping, please, consider correcting the condition so we could assist you.

  43. Hi,
    I want to add cells up, and if they are greater than 0, add those cells up, multiply that time 200,000, then divide by other cells that contain numbers. For some reason, it will not give me the correct answer. Should I be using the IF function before the sum, or the sum function before the if?

    1. Hey, Rob,
      try IF function with SUM function inside.
      If the cells to sum up are A1 and B1, the formula will be
      =IF(SUM(A1,B1)>0, (SUM(A1, B1)*200000)/C1, "")

      If it doesn't answer your request or you meant something slightly different, please, specify your question.

  44. I need this:
    =IF(H18*I18=1,"B18","")
    but B18 doesn't contain a number, but text.
    I need the IF function to give me that text in cell B18 but it gives an error.
    Is there a solution?

  45. I am trying to construct a nested IF AND function that involves summing a range of cells. I tried using the following: =IF(SUM(I6:I15)>15,2,IF(AND(ISBLANK(H24)),0,1.5)) Where am I going wrong or what variation do I need to use? Thanks!

    1. Hi, Adam,
      AND function requires at least 2 parameters, but there’s only one in your formula. If you have only one condition – whether H24 is blank or not – you can omit AND. For example:
      =IF(SUM(I6:I15)>15,2,IF(ISBLANK(H24),0,1.5))

  46. Hey,i am supposed to record the following in excel. Can you assist me?

    THE FOLLOWING QUESTION;
    a) The hostels are allocated as follows
    Kenyan students are only considered if they are Female, 20 years and below, and without kids, such are housed in Nyumbani hostel.
    All other
    Single ladies without kids are housed in Manyaga hostel,
    Single Males without kids are housed in Belobelo Hostel.
    Married without Kids go to Honey hostel and Married with Kids go to Benba Hostel.

  47. i have 3 conditions
    1. if A1-cell value is a +ve value then result in B1 as "Excess"
    2. if A1-cell value is a -ve value then result in B1 as "Short"
    3. if A1-cell value is a 0 value then result in B1 as "nul"

    1. ve? =IF(A1>0,"Excess",IF(A1<0,"Short",IF(A1=0,"null")))

  48. IN A COLUMN I HAVE "1,2,3" AND I WANT TO COVERT THE NUMBERS TO "WORKERS,CLEANERS,DRIVER". BECAUSE THAT IS WHAT THEY STAND FOR AND ALL THE OTHER COLUMNS HAVE TWO OR THREE NUMBERS THAT REPRESENT THE WORDS GIVEN ABOVE. THANK YOU.

    1. Hi, Grace,
      if you want to change the data in one and the same cell and you don’t want to do it manually, you need to use a slightly different Excel function – Replace. It's very easy to use, and here is an article that will help you with the task:
      How to replace one value with another
      But if you have a huge table with tons of values to be replaced, you can try VLOOKUP function.

  49. Hello since I see new posts I will ask. I have tables which I am entering numbers into that total different each time I enter new numbers and total the sheet.

    These numbers for example let's say 11+11+11+11 = 44 are then totalled into a new cell and added with +3. Okay so far I have a number in the example of 47.

    I want to use your formula with the number 47 (remember this will change) that I just generated to recall a value with it.

    Example) On page 2 I have values for each number.
    11 100
    20 300
    35 600
    47 750
    etc

    I want to get the formula to upon result of my number (say 47) to then pull the value associated with that. I've tweaked it to say on a specific value of 47 to pull the 750 into the cell, but that is by manually entering the one value. I need it to do a range of values in one forumla to pull from the list.

    Is this possible? or am I in the wrong formula.

    Thank you

    1. Hello, Dean,

      you were looking for a formula in the wrong article, because you need not IF, but VLOOKUP function.
      For example:
      on 'Sheet7' in A1:A4 we have the numbers. In B1 we put:
      =SUM(A1:A4,3)
      to sum them up and add 3.
      Then in C1 we use the next VLOOKUP function:
      =VLOOKUP(Sheet8!A4,Sheet8!A1:B4,2)
      to return the value of the corresponding number from 'sheet8'.
      'Sheet8' contains a little table with the possible results in A1:A4 and their corresponding values in B1:B4.

      Hope you won't have same troubles in the future!

  50. Hello everyone,

    Please help me to add another formula in this line that word will convert to a value of number. Let's say for example I type word "SL" in the other sheet, then in the other sheet must appear the value of number I set for that word. Thank you.

    Here's the formula:

    =IF('Feb PTO'!M6="","",IF('Feb PTO'!M$5-'Feb PTO'!M6>14,1.2,IF('Feb PTO'!M$5-'Feb PTO'!M6>7,1.1,IF('Feb PTO'!M$5-'Feb PTO'!M6>2,1,IF('Feb PTO'!M$5-'Feb PTO'!M6=1,0.9,0.8)))))

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