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

  1. I have 2 sheet, in first sheet I need to mention "A" or "P" on the basis of 2nd sheet where cell value is in time format HH:MM

    Please help to get t=it donee

  2. Chona:
    Because you have just five ranges to check, I think this will work. Where the value to check is in C29
    =IF(C29<49,0,IF(C29<59,"2%",IF(C29<74,"3%",IF(C29<89,"4%",IF(C29<=100,"5%")))))
    You can enter this formula in D29 and copy it down the column if you need to check values in C30, C31, etc.

    1. Thanks a lot, its working fine.

  3. If the total mark from M60 is 85 then in M61 must appear 5%; or if M60 is 61 then M61 is 3%. I cannot get my formula correct. please help. Thank you.
    Total mark is in M60 cell
    Point % is in M61

    Mark Point %
    0-49 0
    50-59 2%
    60-74 3%
    75-89 4%
    90-100 5%

  4. I need help.

    Lets say I have in column A a have rows with "yes" or "no", in column B values

    A1. yes B1 101
    A2. yes B2 102
    A3. no B3 103
    A4. no B4 104
    A5. yes B5 105
    A6. no B6 106
    A7. yes B7 107

    In column C I need only to see the "yes" positions, but I do not need gaps between rows, which means, that in
    C1 I want to see "101"
    C2 I want to see "102"
    C3 I want to see "105"
    C4 I want to see "107"

    Hope for your help :)

  5. I would like to ask a question :
    I have 4 text alternatives like ( Apple, Orange, Banana and pear).
    I want excel to check a cell and if that cell contains one of them do different sum calculation. for instance if it is apple sum up C1 and D5 and E3 and if it is orange sum C2 and D3 and E1 and put this sum in cell F1
    how can I do this ?

  6. Hi i am lookin to use the function below but it isnt working.

    =IF(B2 = I2,"YES","NO")

    Basically both cells contain text,

    So for example B2 would read "Dispatched" and I2 would read "Delivered" and the forumla would return the word YES

    If B2 would read "Dispatched" and I2 also would read "Dispatched" then the cell would return NO

    Its basically to see if the status of the booking has changed overnight and would be filtered to yer or no?

    Thank you for any advise or help?

  7. I am trying to create a tracking form for specific tasks with SLA's. In one cell, I have a specific task and in another cell the SLA say is "2" days.

    I am trying to get the SLA's to populate when selecting a certain tasks.

    Any help you can provide will be greatly appreciated.

    Thanks

  8. Any help would be appreciated. I cannot figure out how to make this work.
    If C5 = Y and D5 = Y then E5 = L. If C5 = N and D5 = N then E5 = H. If C5 = Y and D5 = N then E5 = M. If C5 = N and D5 = Y then E5 = M.
    Thanks in advance

  9. Logically, I want an excel function to check the value of week number and return the text string from one of Quarter1, Quarter2, Quarter3, Quarter4.

    For this, I'm trying to IF function to return the text string.

    The formula that I have used is -
    (Note-A3 holds the value of week number.)

    =IF(A3=13=25=37,"Q4","Please Check"))))

    The IF function works fine for week number values less than 13.
    However, for values 13 and above, it returns the text string Please Check.

    Kindly help diagnose the problem. And propose if any other function can be used to achieve the same result in a simplified way!

    1. Samrat:
      I'm not sure what you're looking for, but it might be this.
      =IF(A3=13,"Q1",IF(A3=25,"Q2",IF(A3=37,"Q3",IF(A3=56,"Q4","Please Check"))))
      A3 will have to hold these numbers for the Q value to be displayed otherwise the cell will display "Please Check".

  10. Hi.

    How can I make a formula who can do this
    content of cell A1 is 1 then display content of cell D1
    and if content A1 is 2 then display content of D2 etc.
    =IF(A1=1;D1) this works but only for A1=1 how can I put varios
    formulas together
    =IF A1=1 then D1, IF A1= 2 then D2 this one does not work.

    Thanks Rudy.

    1. Rudy:
      Are 1 and 2 the only values that A1 can hold?
      If so, would =IF(A1=1,D1,D2) work? It says, if A1 equals 1 then display D1 otherwise display D2.

  11. if a number 15 digits is correct but less then 15 digits number is wrong how to work out this

    1. Rajesh:
      If I understand your question you want to create an IF statement that checks the number of digits in a cell and returns one thing if it does and another thing if it does not. If that's what you're looking for the formula is: =IF(LEN(D35)=15,"Yes","No")
      This is for a digit count of exactly 15. Any digits more or less will return "No".
      Is that what you want?

  12. IF I WANT GREATER THAN 10 DIGITS AND LESS THAN 10 DIGIT NUMBER IN ONE CELL ?
    HOW TO APPLY FORMULA

  13. Hi,

    Below spreadsheet shows columns A-D and rows 1-9.

    I require a formula that states if A1 is blank & D1 contains text, insert a "B" in column A1 or leave existing values. Thanks
    A B C D

    1 3 K240 K240 - EARTHWORKS

    2 B K250 K250 - Clearing and grubbing

    3 K260 K260 - (a) Soft excavation

    4 3 K270 K270 - BASES 1 - 50

    5 K280 K280 - Blinding

    6 K290 K290 - Reinforcing

    7 K300 K300 - Formwork to sides of Bases

    8 K310 K310 - Concrete to Bases

    9 K320 K320 - Formwork to sides of plinths

  14. Hi,

    I'm curious if it's possible to use an IF function to compare text values.

    For instance, I download emails from one report then enter them in a cell next to the emails from an original report...I'd like to simply enter an IF function of some sort to compare them (as an automatic QC) rather than visually compare.

    Any help would be greatly appreciated!

  15. I need help in figuring out how to write a formula:
    I have four rows and twelve columns for my spreadsheet.
    Two of the rows will have whole numbers manually entered by me and my co-workers.
    I want to write formulas for the other two rows to do automatic calculations based on the data in the other two rows.
    Example:
    If the value of the data in cell A of row four is less than or equal to the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row four.
    But, if the value of the data in Cell A of row four is greater than the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row one.

    Numerical Example:
    Row 1 Cell A we manually enter the number 34
    Row 4 Cell A we manually enter the number 52

    Because (Row 4 Cell A) 52 is greater than (Row 1 Cell A)34, the value that should appear in Row 2 Cell A is (The value that is in Row 1 Cell A) 34

    or

    Row 1 Cell A we manually enter the number 42
    Row 4 Cell A we manually enter the number 36

    Because (Row 4 Cell A) 36 is less than (Row 1 Cell A) 42, the value that should appear in Row 2 Cell A is (The value that is in Row 4 Cell A) 36

    Once I figure out how to write this formula, I know how to write the formula for the cells in row three, which will be a simple subtraction formula.

    Can you help me please?

  16. It’s like a table 1 color print between 36 and 66 is .72

    1 color print between 66 and 144 is .97

    2 color print between 36 and 66 is .97

    2 color print between 66 and 144 is 1.15

    And it keeps going but I would like to populate the amount in one cell. So if I had a 3 color print and it was between 68 and 145 the price would be 1.15
    But if I had a 2 color from and it was between 0 to 68 then it would be 1.20

  17. I’m looking to do a formula . Having trouble with it. What I’m looking for is pricing. If the total units is between 56 and 144 and it is a one color print the price would be .75 but if the units is between 145 and 200 and it is a one color print the price would be 1.00. Can anyone help me with this?

  18. Bayasaa:
    I think this will work for you:
    Enter this in D1:
    =IF(AND(A1="Local",B1="Temporary",C1="Medium"),"Low","Not Low")

    1. It’s excel online so I’m going to guess the most up to date one.. it just frustrating when I’m doing a quote at work that I have to keep looking at the sheet and there is more then one sheet like this so I wanted to put it in excel since that is where I build my quotes.

    2. Oh and that table is 8 x 8
      Up to 8 color prints and 8 price range

    3. No I have never used either one of them before.

  19. Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low". how can i enter the formula in d column?

  20. Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low"

  21. Help!

    Column A Column B Column C Column D Column E
    ABC DEF
    ABC ABC

    In Column E, I'm trying to find a formula that would populate ABC when it's only ABC in one or more cells in row 1; DEF when it's only DEF in one or more cells in row 1; if ABC and DEF are in the same row as it shown in row 1, then I want the formula to return "BOTH". IF statement doesn't seem to work IF(A2:D2="ABC","ABC",IF(A2:B2="DEF","DEF",IF(A2:A2="","","BOTH")))

  22. Help!

    Column A Column B Column C Column D
    ABC DEF IF(I2:M2="ABC","ABC",IF(I2:M2="DEF","DEF",IF(I2:M2="","","BOTH")))
    ABC ABC

  23. IF(A12.5,"1500")))). CAN YOU PLEASE GIVE ME CORRECT ONE...

  24. =IF(A4="GP","3",IF(A4="DMF","4",IF(A4="CARD","5",IF(A4="ORTHO","6"))))

  25. SUB_LEVEL IN_DATE WORK_DESCRIPTION STATUS OUT_DATE
    A 17-07-2018 SKELETON MAKING #VALUE! 18-07-2018

    1).In STATUS Column i want "In Progress" when i enter date in (IN_DATE) Column.
    2). And i want "Pending" in STATUS Column if the SUB_LEVEL Column is empty.
    3). And i want "Finished" in STATUS Column if i enter any date in OUT_DATE Column.
    I have tried using the following formula but it returs an error, Please help:
    =IF($B4"","In Progress","Pending")*OR(IF($E4"","-Done",""))

  26. I would like to create a formula that enable me to do the following

    If col A =Jack is selected (from a list of 10 names) and
    Col B=Cat is selected (from a list of 10 animals)

    Col C= result in a date (from a list of dates)

  27. Hi,

    I have 4 Column, In column A Date, B diffrent Product (almost 10)
    C Purchase Price D Sale Price.
    Daily sale / purchase enter one by one.

    I Want Prouductwise total (Purchase / Sale) in sepate sheet or column

  28. we need a more clear formula for this function

  29. 100 direct 70 Indirect
    if direct method shows 100 value than 70 shows indirect method ?kindly help me

  30. What formula/function should be used on number say
    if 123-45-6789 then SSN and if 66-666666 the EIN

    1. SQL:
      What do you want to do with this data?

  31. I need to create a formula that adds all the S, M, L XL, 2XL in a column. I want them individually, i.e. I am adding tee shirt sizes for a large group, would like an If it equals S, then add them up...

  32. If the "logical_test" is true, I want a value put into another cell. If the "test" is false, I want the words "Out of range" displayed.

    Example: =IF (A13=50,C23=95,"Out of Range")

    That is, if true, the value 95 is put into cell C23.

    How do I write the formula?

    1. Tomas:
      In C23 enter =IF(A13=50,95,"Out Of Range")

  33. i try to figure out a formula

    if A1:A15=A and B1:B15=750 , C,D

    C is value from the other sheet

  34. Hi,

    Please suggest a formula

    A1 value is 50

    If A1 >0 It should be "0" if not it should be "50" (value of A1)

    1. I had the same issue. Here is what I discovered on my own and it works.

      =IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))

      So, for you, I would suggest replacing my cell letters & numbers with yours.
      Maybe try:

      =IF(A10,"50"))

      I haven't tried it yet to say if it will work, so try it.
      What the formula says is this "If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put 50 in the cell".

      I hope this helps.

      1. It took out half of the message that I wrote down. Weird. Let's try this again...

        =IF(A10,"50")) so let's see if typing after it helps to keep the sentence and formula together. lol

        1. This is ridiculous, it did it again.
          Gonna try a new way. I'm going to use spaces to make sure it does it this time. Sigh. Computers, am I right?

          = IF ( A1 0 , "50" ))

          When you type this, remove all spaces. It won't let me type the whole thing without removing half of the formula.
          I can also try this...
          '=IF(A10,"50"))
          I will see if it worked after its posted

          1. OK.. nothing is working. Time to use the last resort

            =
            IF
            (
            A1
            0
            ,
            "50"
            ))

            1. Sorry guys, this stupid blog engine often "eats" the "greater than" and "less than" symbols. So, let me post a formula for this condition:

              "If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put the value of A1 in the cell."

              =IF(A1<0, 0, A1)

  35. I have tried to wrap my brain around this one, any help would be appreciated: If cell C3 contains Doctor then I want B3 do indicate 1, if cell C3 contains Dentist, I want B3 to indicate 2; If cell C4 contains Doctor then I want B4 do indicate 1, if cell C4 contains Dentist, I want B4 to indicate 2 etc. as per the list below etc
    1 Doctor
    2 Dentist
    3 Pharmacist
    4 Optometrist
    5 Veterinarian
    6 Consultant
    7 PME
    8 Other

    Thank you !

  36. I am kinda stuck with creating a formula. I want something that, example: IF A1 is equal to or greater than (numbers in Column E) then show cell in Column E that is Greater than A1. So basically I want it to show the next highest number. I am working with dates here by the way.

  37. Hi, I need the help of the experts! I've been tasked with creating a weekly time sheet for employees at my company, so I'm trying to figure out how to write a IF formula that sums up the totals of daily hours worked and if greater than 40 hours the remainder(overtime) goes into another cell, if less than 40 then the actual sum goes into the cell. In other words, I want cell C13 to have the sum of H29:N29 and if that value is over 40 then the remainder would go into cell C14. Is this possible?

  38. Hi everyone,

    I want to take the AVERAGE of any cell if the Campus is X
    So, If Column C is my Campus and Campus=DEN (any value equal to DEN $C) will take the value of G$ and average it. This formula will be in a separate cell.

    Is this possible? If so, and I sort the table will the formula continue to work?

    Thank you for your help.

  39. Hi Team,

    I am trying to build a formula to tell if a given zip code falls within a list of zip codes. EG If I input 11201 in cell A1, I want A2 to tell me "Yes" or "No" if it falls withing a list of given zip codes.

    If it helps, the list of zip codes are: 11201, 11205, 11206, 11211, 11213, 11216, 11217, 11221, 11231, 11238, and 11249.

  40. I have an issue with graphs in Excel. I have 2 workbooks, workbook 1 is the master and has line graphs on sheet 1 and data for the graphs in sheet 2.
    Workbook 2 is an exact copy as far as the graphs and data is concerned. I don't want to enter the same data twice so I am using this formula to copy values of data from workbook 1 to 2 and it works. =IF('[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,'[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,"")
    But..... The data is from January to December. I have only just started to use this formula in wb2 and when I copy it over from July to December the graph shows zero's for those 6 months which I don't want it to do as it affects the linear trend line I'm using.
    Is there a way of having the formulas in wb2 and the graph only showing results if there is an actual value in wb1.

  41. Hi Team,

    I need a big help.

    If the first column contains a value of months like January, February,etc and the second column has years like 2016, 2017, 2018, etc.. and at the same time i have a different sheets for each month and year for example let we take the attendance and payroll sheet, If i want the data for the number of days present for the month of February 2018 to be displayed in the 3rd column so can you guys please help me out for the same.

  42. Hi guys

    I have column A and B.
    As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*

    Example:

    Column A column B

    70591- Completed 80042-completed

    Column C

    Yes

    1. Wicks:
      Wildcards can't be used in IF statements. Will it work for your situation to split the "70591-" out into another cell and build the IF Statement for the cell that contains the word "Complete"? If that will work then you can easily build an IF statement to check for "Complete".

  43. Hi guys

    I have column A and B.
    As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*

    Example:

    Column A column B

    70591- Completed 80042-completed

    Column C

    Yes

  44. Same WorkBook with a number of worksheets which are all timesheets. In V25 on the previous worksheet a formula which happens to be =U18 returns a value in time of 13:30. This means I have done 13.5 hours overtime this month. I want to carry that figure forward to the next timesheet (new worksheet cell B11). We are only allowed to carry over a maximum of 14.5 hours. I would therefore like the formula to say =IF'14 MAY 18'!V25 is less than 14:30, then return the value of V25. In this case 13:30.
    However if'14 MAY 18'!V25 = 14:30 then return 14:30.
    However if,'14 MAY 18'!V25 is greater than 14:30 then only return 14:30. I then need to work out another formula that shows how many hours lost (if any).do another formula to work out how many hours lost in B12, if any at all.

    We got as far as:
    CELL B11 I had =IF('9 JUL 18'!V25>14.5,"14:30",'9 JUL 18'!V25)

    Ell B12 I had ='9 JUL 18'!V25-'6 AUG 18'!B11

    But it doesn't work as you can probably tell already! Your kind assistance would be greatly appreciated. With thanks K

  45. I've got a formula that isn't pulling my true value correctly and I'm sure it's a minor formatting issue.

    =IF(P2> 0,"P2", "0.00")

    Basically, if "P2" is greater than zero, I'd like it to show whatever value is in P2. If it's less than zero, I'd like it to just show "0.00"

    It shows the false value just fine, but shows the literal text of "P2" when my scenario is true.

    1. take out the quotes on P2

  46. Not sure if this will work, but...
    I want to know how to write the following;
    =if(left a1, 1) = A, or B, or C, then "ON"

    It's not working for some reason... help??

  47. hi i want to insert formula excluding 0 but including number 1 to 4 ....plz help

  48. I'm trying to get a cell to work with several other cells and I can't figure out how to do it, because I also need to use + and - inside the formula.

    I'm trying do something like this, but the word True or False keeps showing up in the cell, instead of the reference cell number.

    =IF(U134>U159+100,"",IF(U134<U159-100,"", Need missing formulas here))

    What I am trying to figure out:
    If cell number (U134) is greater than cell U159+100, then keep the cell blank. If cell number (U134) is less than cell U159-100, then keep cell blank. If the value of (U134) is equal to or above U159(but below U159+100), then display the value in cell U134. If the value (U134) is equal to or below U159(but above U159-100), then display the value in cell U134.

    Basically, if it is outside a range that I set +/- 100, then I want the cell to be blank and if it falls within the range, then I want the cell to display the reference number (U134). I don't want TRUE or FALSE placed into the cell, just leave the cell blank or have the number I am referencing it to.
    I can't figure out how to do that. Is there a way I can get it to do what I need it to do? Help please.

    1. Never mind. I figured it out myself. Tough one though.
      Here is the formula if anyone is having the same issue:

      =IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))

      I guess I was trying to make it harder than it was, by trying to get the cell to add a lot more commands than necessary.

  49. Need formula for following
    Sheet 1:
    Date, Invoice No, Customer Name, Due Date, Amount
    Sheet 2:
    Customer Name, Customer ID, Due Days

    In Sheet 1 Once Date entered, Due Date column should automatically update by matching Customer name from Sheet 2 adding due days to Date.

  50. hi am trying to create a time sheet to calculate working hours and my start time will be in one day and my end time will be in another day =IF(G28="","",IF(G28-F28>0.208333333333333,G28-F28-0.0208333333333333,G28-F28)) but i cant seem to work it any help would be great

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