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 52. Total comments: 4817

  1. Good afternoon,
    Cell A1 = 2 Sizes of mixed, ranging variable number.
    (Example:&UA63%38TL & E1D&HX1)They change radically inside there 10 and 7 digit format respectively. Tracking each number is impossible, but the total number of digits remain constant; 10 and 7)
    I need to get a True/False for a cell with a specific digit count in a cell.

    I hope that was clear enough to understand.
    Thank you!

  2. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

    1. Hi,

      I am sorry, your data look distorted in the comment above. If you can send us a small sample workbook with your source data and the result you want to get, we'll be able to help you better.
      Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.

      Thank you.

      1. thanks you so much for reply Ekaterina Bespalaya

  3. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  4. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  5. if I want to find if C1 is between 200 and -200) put "Lab" and if G2 is "PD25088" put "GEO"

    1. Your question is unclear because the connection between the two outcomes is unclear. I can't figure out the logic.
      However, the first part will require =IF(AND(C1-200), "LAB", "No LAB").
      The second is =IF(G2="PD25088","GEO","No GEO").

  6. I need formula in excel

    in entire row if same number - count only 1 and other show as zero

    111 - show as 1
    123 - show as 1
    124 - show as 1
    124 - show as 0
    125 - show as 1
    125 - show as 0
    126 - show as 1
    125 - show as 0

  7. i want to use that if condisation

    like when i type company name it has to show the GST no.
    i have so meany clients so thats way mail me or msg me how can i use that condisation 8197938637

  8. Hi! How can I create a formula for the following?

    =IF(H9="S4309","",IF(H9="S4310","",IF(H9="S4311","")=I9+365))

    So, if cell H9 equals either S4309,S4310 or S4311 then cell K9 will be blank. Otherwise, it will populate an expiry date based on cell I9.

    Thank you for your help!

    1. I believe this is what you're looking for.
      =IF(OR(H9="S4309", H9="S4310",H9="S4311"),"Blank",I9+365)
      I put the word "Blank" in there to test it. You can use "". I formatted I9 as Date and H9 as Text.

  9. Thank you for the lovely examples of using the IF() function. I'm going to use this for a lesson on teaching the IF() function.

  10. Please ignore my first post, as I have worked out the formula, but realised I cant sum the values in the target Cell

    So this what I am trying to do now.

    If C27 = P then S27 = 1
    If C27 = DO then S27 = 0

    Is the above possible.

    What is the best way to combine into one argument.

    Thanks.

    1. Looks as if this will need a nested IF formula. Ablebits has a good description of how to use these as well as similar conditions. Search "Nested IF formulas in Excel" here in Ablebits.

  11. I can get this working as individual formula's

    =IF(C27="P", "1")
    =IF(C27="DO", "0")

    What is the best way to combine into one argument.

    Thanks.

  12. How to write logical formula for the following ?

    If sales is 101% to 104 % of Target , incentive will be 1% of Target value
    If sales is 105% and above Incentive will be 1.25% of Target value

  13. Hi Team,

    I am trying to find a substring from a string of URLs

    example : I have column (a) with codes "xhd2424" and coloumn (b) with URLs "http://www.xyz/today_same-xhd2424". I am trying to see if the code in column (a) exists in column (b). If so I want to print "true" or "false" in a different column.

    I have been using the following formula " =IF(ISNUMBER(SEARCH(A2,B:B)), “Yes”, “No”)" it seems to work on some spreadsheets and for some i get an error "#Name". I am not able to figure out the issue with the formula. Only that the code in the URL doesn't always be at the end, it is embedded in the middle for some.

    Please help.

  14. Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00 : $10

    How do I write the logic formula?

  15. Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00

  16. Can I use cell/text formatting as a condition for an IF expression? For example IF the content of a certain cell (A1) is formatted as strikethrough THEN contents of the current cell (B1) = a value (1)

    Thanks for your help.

  17. Hello I need some help with my excel. I have a default value of 3 on A1 then if B2 is greater than 0, then i need to deduct 1 from A1. Thanks guys

  18. Hi,
    If i have a list of text and I wish to enter the blank cells with text and leave the other cells with there original text is there a formula for that.
    I have created a True false column as I thought this might help but am stuck. I can enter the true statement to add the text that but the false argument I am stuck on.
    Regards Sara

  19. Hi, please help me whit a formula for my need

    I have a cell that can be text or number ( AH99). I need to make an IF AH99 is any text, then return 0 and if not, multiply that number from AH99 whit another cell ( AI99) and return result

  20. Hi Team, You guys/gals do a great job. I hope you can help me.
    I need to check for duplication in a list of names. Column A contains the primary name. Column B through H contain other contacts. I need a way to check if any of the other contacts equal the primary name.
    Example: Adam, Bob, Jason, Rick, Susan, Adam, Ray, Zak. (the response here would be a fault.
    I tried: +IF(B2:H2=A2,"OK","Fault") but I get an error message. I also tried: +IF((B2:H2)=A2,"OK,"Fault")
    I know I could compare each of the other contact cells individually, but that would be inefficient.

  21. Hi,

    I have a question if there a same question but I have different answer for same as below which formula can I use.
    data Answer
    no water Vavle off
    no water empty
    no water abc
    no water cyzdf

  22. Chargeable Wt| |Handling & Doc| |Custom Clearance| |Transport Charges|
    335.5

    I wanted to put the values once chargeable wt key in but the formula that i try to input result is 0...

    actually i want to set like this - if chargeable wt less than 233.33 charges charge to us MYR21.00 but if move than 233.33 they will charge us MYR0.09 per kg.

    Can you help me..

  23. I need help with this formula: =if(B5:E11>100,"Yes","")

    I wanted to put the values if any from the test in the G column. I tried the formula in cell G5; selecting the G5:G11 in the G column and typing the formula in formula bar; and selecting B5:G11 with If from Autosum list.

    I get an #Value! message in each cell selected of the G column every attempt.

    What am I doing wrong and how can I fix it please?

  24. HI

    If TEXT IN A1=B1=C1 THEN THE SAME RESULT IF TRUE ELSE FALES

    PLEASE HELP HOW TO USE IF CONDITION

  25. I am trying to put a formula for multiple spreadsheets, where on spreadsheet A the date indicates something as completed and a blank spot indicates something is incomplete using - spreadsheet B contains the date/blank spot. So essentially spreadsheet A is meant to be a summary of spreadsheets B-G (B-G have the data).

  26. Trying to come up with a formula that says, if the number in cell A1 is greater than 1, but less than 500,000 the result is 120.

    Similarly, if the cell in A2 is greater than 500,001, but less than 1,000,000 the result is 200.

    Thanks in advance.

  27. I am trying to put a formula where the date of one column is more than or equals to todays date then it should display as "not expired" and if the date of one column is less than todays date then it should display as "expired".

  28. Sir
    I want formula for
    100g 36pkt
    Open stock 10cb
    Production 5cb+45pkts
    So for total stock 45pkts-36pkts difference to be converted into cab(10+1=11) and at pkts 45-36=1 pkt for doing this how to put formul
    Regards
    Ramachendir

  29. Hi
    I'm trying to type a if statement for text with the following criteria:
    if a cell begins with ="GA" then "General Assortment"
    if a cell begins with "7" or "9" and is 10 characters long then equals "Planogram"
    If cell is between "1" TO "9999" then store layout module

  30. Q: Please help me translate this into formula. I'm really having hard for the then statements that are equal to computations.

    Statement: << If Hours >

    Letter Assignments: Hours is B4 | Rate is C4 | Gross Pay I4

  31. my quay for 1 person 28 days present so we have give to extra bonus amount 10 Sapphose he is not 28 days so how can give me bonus amount

  32. Hi, I'm trying to figure out a formula to list the date in the text. If B25 is a date value, then if it has passed to display a comment that lists the date. When I use this text, it shows Date B25, but I want it to be entered as an actual date value or to come out black if it's not expired. With the below formula it displays Contract expired on, DATE(25). Instead of listing the date.
    =IF(B25<DATEVALUE("03/18/2018"), "Contract expired, DATE(B25)", "")

  33. I am trying to count the number of days between two dates but want the return value to be 0 is there is no date in one or both of the cells. So far I have;

    =IF(OR([@[Date Required]]"",[@[Date Passed to Ops]]""),NETWORKDAYS([@[Date Required]],[@[Date Passed to Ops]],-1),0)

    But I am getting a #NUM! error, what have I done wrong?

  34. need a formula for , any hours over 8 in cells D thru H would be added to the overtime cell (K)

    D E F G H total reg ot

    8 8.75 8.00 9 8 41.75 40 1.75

    1. =SUM(IF(E197>8,E197-8,),IF(F197>8,F197-8,),IF(G197>8,G197-8),IF(H197>8,H197-8,),IF(I197>8,I197-8,))

  35. Hi,

    I want to use 'if' formula, I have 5 slabs 500000 to 999999 = 1%, 1000000 to 1499999 = 2%, 1500000 to 1999999 = 3%, 2000000 to 2490000 =4% & 2500000 to above = 5%
    so how to use IF formula, pls help me...Thanks

    1. =IF(AND(D193>=5,D193=10,D193=15,D193=20,D193=25,"5%","")))))
      please change the values accordingly.

  36. Hi,
    I am new to IF Function. I am trying to create formula to find the Turn around time (tat).

    A COLUMN = DATE START
    B COLUMN = DATE END

    IF DATE END IS BLANK THEN TAT FORMULA IS TODAY()-DATE START
    THEN IF DATE END IS WITH DATE IT SHOULD BE DATE END MINUS DATE START

    WHAT WILL BE THE FORMULA FOR THIS USING THE IF FUNCTION?

    1. Hello,

      Please try to use the following formula:

      =IF(ISBLANK(B1),TODAY()-A1,B1-A1)

      Where B1 is the cell with the end date, A1 is the cell with the start date.

  37. Hi,
    I'm new to IF function, I'm trying to write a formula but there's a problem on it, I need help.
    A = Date 1
    B = Date 2
    C = Status
    D = No otherwise overdue

    =IF(AND(A<TODAY(),"Overdue","No"),IF(AND(B<TODAY(),C"Approved & Closed","No","Overdue")))

  38. Hi Team,
    I need a formula for automatic calculation of age cycle..! Below mentioned example for your reference..!
    I have to convert data from below mentioned age format to cycle (below 20, 21-25, 26-30,30 above.)

    25yrs7m8days
    31yrs11m3days
    18yrs4m21days

    1. Hi,

      Suppose your data are in column D, please try to enter the following formula in cell E1 and then copy it down along the column:

      =IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=20, "20 and below", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=25, "21-25", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=30, "26-30", "30 and above")))

      Hope this is what you need.

  39. Hi,
    I have 3 columns and I have to create another column by concatenating :

    Last Name First Name DOB Alt

    Addison Ashley 10/12/2012 AddAO12
    Aguilar Jayden 7/2/2013 AguJ702
    Adkins Skyhe 12/28/2012 AdkSD28

    So in the Alt column I know how to get LastName and firstName but need to display month in letters as O for 10(october) , D for 12 (December) if birthday month is 2 digits and if single digit month the single digit has to be returned.

    Thank you

    1. Hi, Reena,

      I'm afraid there is no easy way to get the output you need using a formula. You'd better use a macro. I'm sorry, we can't help you with this, please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

  40. I track mileage and other data on an Excel spreadsheet. I know the round trip mileage for each city I go to. I simply want it to autofill the round trip mileage of the city I type in. For example:

    If, in row 10, I type W.Roxbury in column A, I want Excel to autofill 58 in column B. If, in row 11, I type Sturbridge in column A, I want Excel to type 116 in column B, and so forth for each individual row.

  41. Coll A Coll B Coll C
    Y Y
    N N
    N Y

    I am trying to write an IFs formula for Coll C, that returns a Y if Coll A & B are Y, a blank if Coll A & B are N, and a N if Coll A is N and Coll B is Y.

    1. Hello, Joe,

      Please try the following formula:

      =IF(AND(A1="Y",B1="Y"),"Y",IF(AND(A1="N",B1="Y"),"N",""))

      You haven' t mentioned what the formula should return if Coll A is Y and Coll B is N. Currently the formula returns nothing in this case. If you want to see N or something else in column C when this condition is met, you can just add IF(AND(A1="Y",B1="N"),"N" before the double quotes at the end of the formula above.

      Hope this will help.

  42. I'm trying to populate a manning layout.
    what formula would work across multiple tabs?
    Example:
    cell 1 = 1 for shift
    cell 2 = 4 for position
    cell 3 = 6 for area

    cell 1 & 3 moves name to spreadsheet in correct tab and cell 2 is what cell in that tab it populates.

  43. Please explain how can i have three answer using two columns as follows,

    Ex: in 1st column = column A and B if >0 = "yes", if not "NO"
    if it is a figure only in Column B the answer should be " Loading",

  44. Hi,

    in cell A1 value is 1000. In cell A2 value is 12. now I want formula in Cell A3. Formula would be like that.

    IF CELL VALUE OF A2 12 BUT 18 BUT <=24, THEN A1*3%.

  45. Greetings,

    please guide me on how to create the following formula: if the value of the cell is in (minus) then multiply by the value of a different cell. Let me know if it is possible.

    Thanks alot

    1. =IF(D177<0,D177*F177,"")

  46. Hi
    Kindly explane what is the meaning of this formula =if(P12<=0,R12,P12*R12)

  47. Greeting sir
    Kindly explane what is the meaning of this formula =if(A12=false,C12,A12*C12)

    Thank you

  48. Can I return a text statement and a cell value in the return value of an If statement? For example if the logical test is false can my false value be a text with a cell value? i.e value_if_false is "Please change parameter to D19" where D19 is a cell value.

    syntax =if(D18=D19,No change necessary, Please change value to D19)

    Where D19 is the actual cell value that gets displayed and not the text D19

  49. What is the functionality of Marcor in excel, how macro creation help us in sorting data?

  50. Hi,

    I have a set of four cells that return either text or a number. I have a warning that arises if ALL of the cells are text, otherwise no warning (simple if statement - =IF(AND(ISTEXT(B5),ISTEXT(C5),ISTEXT(D5),ISTEXT(E5)),"WARNING:",...). For the false argument, I need something very specific. I need to produce another warning if ONLY ONE of the cells listed is a number, but as soon as two or more are numbers, then I want to do something else. How do I say, "Do this when ONLY EXACTLY ONE of the cells is a number"?

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