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 120. Total comments: 4832

  1. Hi Svetlana cheusheva,

    I have employee competencies for each position in worksheet and I want when I select the position in the other worksheet, I want all the competencies appear automatically. what is the formula please

  2. Hi Svetlana cheusheva,

    I need your help. I want to have a formula based on the appraisal rating. i.e AI IS 0 UNSATISFACTORY (no increment), A2 IS 1 TO 1.4 SATISFACTORY (increment from 1% to 3%), A3 IS 1.5 TO 2.4 GOOD (increment from 4% to 7%), A4 IS 2.5 TO 4.4 VERY GOOD (increment from 8% to 10%) AND A5 IS 4.5 TO 5 EXCELLENT (increment from 11% to 15%). My question is I want to have a formula in increment coloum which once I put the rating, automatically the increase amount % will show.

    for example an employee rated between 1 to 1.19, his increment will be 1%, if he rated 1.2 to 1.29, his increase will 2%, if he rated between 1.3 to 1.4%, his increase will be 3%

  3. Hi i want to change the Text with one excel sheet but i also want the text should change in other two sheet where the same date i have. but the other two sheet contain same data but there position is not same with the first one. for example I have a word " World" in column A and cell 1 but in other sheet i also have a word " World" in Column A but It's Cell in 8. Can you help me.

  4. please how do i create an IF function that denotes "1" when the response in a cell is "yes" and "0" when "No" is keyed in?

    thanks

    1. Hi Nonso,

      Here you are:

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

  5. Hi Svetlana cheusheva....
    how to use if condition in my ques.......
    plz tell me......
    * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

    plz tell my frnd svetlana.......

  6. who to convert 5421 to five thousands four hundred twenty one in excel

  7. Hi Svetlana cheusheva....
    how to use if condition in my ques.......
    plz tell me......
    * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

    1. plz tell me frnd svetlana.....

  8. Hi!,

    I am trying to make a formula that will return the calculated value from another cell if a "check" boxes has a √ or even simple X in it, but return blank or 0 if left unchecked. I used =IF(B2="x",A1,0) where B2 is the bordered "checkbox" and A1 is the value from formula calculation in that cell.....

    What I get is "0" if ii put an x in the box and "-" if I leave it blank/unchecked?!

  9. Hi Svetlana cheusheva....
    plz giving to my ques. ans....
    * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

    1. how to write in if condition..... my ques...

  10. Hi,

    I am looking for formula with multiple if conditions.

    some what like this;
    =if(J2="Chemicals","Heavy Industries->Chemicals"),if(J2="Medical Devices","Healthcare->Medical Devices)

    but this one does not work. and I have to apply same for another 7 conditions.

    Can any one help.

    1. Hi Sam,

      The correct syntax of nested IF's is as follows:

      =IF(J2="Chemicals","Heavy Industries->Chemicals", IF(J2="Medical Devices","Healthcare->Medical Devices", ""))

  11. Please can you help me?

    need to calculate if a cell (D58) has inbetween 6 and 12 in then it should equal F7 (which is £25.58).

    The cell before i need to know if the number was 5 or under so i =SUMIF(D58, "<=5",F6:F6) which is working fine.

    After i have figured out the 6 - 12 one i need to do the same for 13-18 and then one for 19 +

    1. Hi Laura,

      >if a cell (D58) has inbetween 6 and 12 in then it should equal F7 (which is £25.58).

      You can use the following formula:

      =IF(AND(D58>6, D58<12), F7, "")

      And for multiple conditions, you can use nested IF's:

      =IF(D58>19, F9, IF(D58>12, F8, IF(D58>6, F7, F6)))

      If D8 is 5 or under, return F6
      If D8 is between 6 and 12, return F7
      If D8 is between 13 and 18, return F8
      If D8 is 19 +, return F9

      1. Thanks

  12. Hi all,

    does anybody know if a formula exists that could have with the following scenario....

    if I have a cell with the text "19mm & 12.5mm" can I get another cell to display the same value which is shown in another cell.
    for example...
    I have a cell which is linking to a sheet and returns the calculation of "38" - I basically want that same figure (whatever the calculation is) to displayed in the box below it IF a certain cell displays the text "19mm & 12.5mm"

    1. Hi Daniel,

      Supposing that A1 is the cell with "19mm & 12.5mm", and B1 contains the result of the calculation you want to display (e.g. 38). Then you can use the following formula:

      =IF(A1="19mm & 12.5mm", B1, "")

      1. HI..
        I want to use if function with RAND() Function...

        I want to use =RAND()*7+$C$12 in cell C11
        if cell C12 is greator then zero...
        otherwise Result should be zero in cell C11

        help me..

        1. Hello!
          If I understand your task correctly, the following formula should work for you:

          =IF(C12>0,RAND()*7+$C$12,"")

  13. Hi Svetlana, Hope your are doing great.
    Actually I have two columns and want to get the result in a different Workbook.
    Suppose Data is as follows:
    Month Velocity
    June 6.5
    July 8.0
    July 7.2
    August 8.8
    September 7.4
    I need to get the average of the 'Velocity' of the specific month.
    In the case above there are two instances of 'July' so when I get the value of July in the 'Month data' workbook, the cell should display the average of 8.0 & 7.2, in case there are three instances then it should consider those three values for average.
    Can this be done dynamically using some formula?
    Thank you

  14. * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

    1. please giving answers to me....

  15. If B70 1600 then "Scania" or if value is 1000 then "Merc 1323" or if B70 is 450 then "merc 818" and so on.

    Please help

    1. Hi Shelly,

      Our blog engine often swallows the "<" and ">" symbols, sorry for that.

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(B70<450, "merc 818", IF(B70<1000, "Merc 1323", IF(B70<1600, "Scania", "")))

  16. comment should read B70 less than 1600 it's not shown up the symbols for some reason

  17. Hi Svletlana, Please help me with the following formula,

    Im trying to get the cell to tell me which vehicle I will need based on the weight of the customers goods. total weight of customers goods is in cell B70.

    eg. If B701600 then "Scania" or if value is 1000 then "Merc 1323" or if B70 is 450 then "merc 818" and so on.

    Please help

  18. Need help with a formula. I keep getting an error. What I want to do is IF the value in I2=M and F2(numerical) < 8 then I would like the value to = Yes else No. The formula I thought I needed was

    =IF(I2="M" and F2 < 8, "Yes" , "No")

    But this doesn't seem to be working. Also can I compound these with something like:

    =IF(I2="M" and F2 < 8, "Yes" , "No") or (I2="S" and F2 < 3, "Yes" , "No")

  19. This post was really helpful. I thank you wholeheartedly.

  20. Hi Svetlana, Hope your are doing great.
    Actually I have two columns and want to get the result in a different Workbook.
    Suppose Data is as follows:
    Month Velocity
    June 6.5
    July 8.0
    July 7.2
    August 8.8
    September 7.4
    I need to get the average of the 'Velocity' of the specific month.
    In the case above there are two instances of 'July' so when I get the value of July in the 'Month data' workbook, the cell should display the average of 8.0 & 7.2, in case there are three instances then it should consider those three values for average.
    Can this be done dynamically using some formula?
    Thank you

  21. please me in constructing the formula...example i have here grade of %Ni and %Fe and i want to determine it directly where does the % falls in example for %Ni=1.26 and %Fe=25.79
    OV1 15<44
    OV2 =45<46
    L1 =48
    L2 =46 =1 =25
    S1 >=1 <1.5 1.5 =2
    BR <1 <15

  22. I need H5 to be multiplied by 6% if > A1 and multiplied by 7% if > than A2 and multiplied by 8% if > A3.

    1. Hi Kristal,

      Here you go:
      =IF($H5>$A$1, $H5*6%, IF($H5>$A$2, $H5*7%, IF($H5>$A$3, $H5*8%, "")))

  23. thanks! - sent you the info via email

    1. The problem was with percentages entered as text strings. Emailed you back the fixed sheet.

      1. Thanks so much for the help! appreciated!

        Now i have a question around "if i can't get the text to percentage on the sheet to work unless i double click the box and click on the accept icon" (tick box image).

        Is there a way to change format quicker on the sheet.

        I've tried selecting the column, going to number format and changing to percentage - but then i still have to click in each box to accept it, before the formula works. Trying to see if i can save myself time and do it with a shortcut.

        Thanks

        1. In the worksheet you sent to us, I fixed the percentages in the following way:

          - Select column H (the Percentage column), press Ctrl+H and replace % with nothing (leave the "Replace with" box empty and click the Replace all button).

          - In some empty column in row 2, enter the formula =H2/100, copy the formula down to other cells, copy the column with the formula, then select column H, right click and select Paste Special > Values. The result would be decimal numbers like 0.75.

          - Apply the Percentage format to column H.

          Done!

          1. It was and great thank you - it fixed it perfectly!!

  24. Hi Svetlana

    Thanks but unfortunately that doesn't work either, tried it and even if i have a 10% on the probability - it still brings up the number.

    1. Hmm... this is very strange. The formula works just fine for me. If you can send us a sample workbook, and we will try to figure this thing out.

  25. Hi

    I'm trying to get a formula to work this is the detail:

    Column G = sales
    Column H = probability
    Column J = status

    I'd like to get a formula to work if its status is open and is above 75% then it returns the sales number - i have this but it won't work
    =IF(AND(J300="Open",H300 >75),G300,"")

    1. Hi Ash,

      Your formula is correct except that you omitted the percentage :)

      =IF(AND(J300="Open",H300>75%),G300,"")

      If you'd rather not use the percentage char in formulas, then it should read H3>0.75.

  26. So I entered the formula to have a cell name itself the workbook name which is a date. The other cell that I have turns into the day of the date. But since I have done = it only reflects the date not the day.

    1. Hi Justin,

      Sorry, I am not sure I can follow you. If the problem is just with displaying the date rather than the day of the week, select the cell, press Ctrl+1 to open the Format Cells dialog and select the desired date format there.You can find the detailed steps and lots of examples in our Excel Dates tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/#custom-date-format

      If you have a different issue, please clarify.

  27. If we have two different worksheets.For example Workbook1 contains weights 0.5,1,2,3 and other worksheek contains the master that means that fine contains 0.5 value 10.00,1 value 20.00,2 value 30.00 and 3 value 40.00.

    And i want to use formula to upload the same figure in Work sheet 1 also as a value that means if the weight is 0.5 that value should be 10 like that how to make formula

  28. HI there,

    can you help me with this project?

    IF (A2 >26, A242, A263, A284,A2<104),4 "")

    I appreciate your kind help

    1. (A2 >26, A2>41, A2>42, A2>62,A284,A2<104),4 "")

      1. Hi Fahad,

        Sorry, I cannot understand the conditions. Can you elaborate please?

  29. Helooo,

    how can utilize this formula in my office reminders.

    I have certain daily task that is fixed in every month. let me know how can I create reminders, that today I have particular task to perform?

  30. A1= Level 33
    B1>50%=YES and B150%=YES and C1<50%=NO

    1. I am not sure I fully understand the logic. Nevertheless, here's the formula as per your conditions:

      =IF(B1>50%, "YES", IF(C1<50%, "NO", ""))

  31. hi there,
    Searching for help,
    I wanted my data to produce end result YES or NO:
    must meet condition as below:

    A1= Level 33
    B1>50% = YES and B150% = YES and C1<50%= NO

    please help , thanks!

  32. I need help with finding the sum of the following:
    If there is text in C10 and C11, I need there to be a value of 1 for each to calculate in F4, otherwise = 0.

    1. Hi Elaine,

      I can suggest the following formula:

      =IF(AND(C10<>"",C11<>""),2,IF(OR(C10<>"",C11<>""),1,0))

      The formula returns 2 if both C10 and C11 have values in them, returns 1 if one of the cells has a value, and if both cells are blank, 0 is returned.

      If you are looking for something different, please clarify.

  33. Column E has a product Names (X and Y) with the corresponding Value ($ amt) in Column G, If the product name is X I want a formula to return the value as (X*70/100) or Else Blank. The result is expected in column H. Please Assist.

    1. Hi!

      Try this one:

      =IF($E2="X", $G2*70/100, "")

  34. i would like excel classes

  35. Need help,

    I've a data in d2:d in which sometime there might be empty also(blank).So with reference to these I want information of a2:a but if d2:d have data I didn't want information of a2:a, I only want a2 information if d2 is blank other wise next skip a3 if its same then a4 and so on?

    1. Please help me in this regard.

      1. Hi Insu,

        I am not sure I can follow you. So, let me check if we are on the same page. You want the formula to return a value from A2 if D2 is blank, right? If so, then you can use the following formula:
        =IF(D2="", A2, "")

        If you want to pull a value from D2 if it's not empty, then use:
        =IF(D2="", A2, D2)

        1. Thank you

  36. I'm working on a spreadsheet that will have a $ amount in column C, one of a possible 4 employees names in column D, a cumulative running total in E, and then,at the bottom of the whole thing, a running total for each individual employees sales. I know basic formulas, addition, multiplication, etc., but I do not know how to do an IF formula such as this. Can anyone help? Thanks!!

  37. I want to calculate if A1>=1.5, but <=2 then yes

    1. Hi Tshepo,

      Here you go:
      =IF(AND(A1>=1.5, A1<=2), "yes", "")

  38. Hi, Svetlana

    Below condition is not working

    =IF(OR(A1=c1,B1*F1),IF(A1=C2,B2*F2),IF(A1=C3,B3*F3))

    Can you please correct me.

    Column A Column B Column C Column F
    B81234 16 B91456 $8,995.00
    B81345 19 B81234 $4,887.50
    B91456 27 B81345 $5,391.00

    Regards
    Manish

    1. Hi Manish,

      Try to put it this way:

      =IF(A1=C1, B1*F1, IF(A1=C2, B2*F2, IF(A1=C3, B3*F3, "")))

  39. Perfect it worked.

    =COUNTIF(M95:M106,"Darryl*")

  40. Hi Svetlana, I am using the formula below look for a specific that could be listed in the cell range listed below, such as the name Darryl that name could be clustered in with multiple names in one cell, But I need to count the name as one. I hope that makes sense

    =COUNTIF(M95:M106,"Darryl", "Darryl Sally George")

    1. Darryl, how did you get a response for svetlana so qwick? I have tried since last week.

      Maybe you can help.
      Hi, Svetlana.
      I have in mind a (maybe an IF) formula I want implement.
      In a cell I have a date of 6-17-15, in one cell (oh,say E7) when we started tracking an item as "Open". Cell F7 (7/7/15) would be the date we closed it. Cell G7 would tally the days it was open. If we closed it on 7/7/2015, the duration open would be 20 days and the formula would be simply be =F7-E7. Correct?
      But, let's say we had not (closed it) filled in the Cell F7 yet and it is blank to visually show it as outstanding. How would I express the formula in Cell G7 to keep a tally of the open days. Then when we fill in the (or closed it) cell G7, it will correctly fill in the open days?

      1. Hi!

        I am sorry for overlooking your question. The notification never reached my inbox :(

        If my understanding of the task is correct, you need a formula that subtracts E7 from F7 if both cells have dates in them, and if there is no date in F7, then E7 is subtracted from today's date. If so, the formula is as follows:

        =IF(F7="", TODAY()-E7, F7-E7)

        Please correct me if I am missing something.

        1. It works great. Thank you.

    2. Darryl,

      The COUNTIF syntax allows for only one criteria. To count partial matches, you can use a wildcard char, like this:
      =COUNTIF(M95:M106,"Darryl*")

      Or =COUNTIF(M95:M106,"*Darryl*") if the name "Darryl" may be preceded and/or followed by other characters, e.g. "Mr. Darryl George".

  41. Hi Svetlana, you are the best that did work..

  42. I am try to count how many "Y"'s in a range of columns and convert that into a numeric value and place that value in a different cell.

    Please help

    =IF(SUM(D95:D106)="Y", "1")

  43. Using Logical "IF" formula to determine if date in one cell is < date in another cell =IF(A1<B1,True,False). Returns the correct answer, however when I try to copy it to multiple rows....it does not! If however I go to each row and click the Insert Function menu option...it will change the answer to the correct one! Can't seem to figure it out! Please help

    1. Hi Scarlet,

      Usually copying a formula by selecting the cell and dragging down the fill handle works without a hitch. Are you coping it to other cells in the same column, or somewhere else? For example, the formula is in C1 and you need to have it in, say, C1:C100?

  44. I am trying to add all of the values in column E that have the corresponding number 53017 in column G. I tried this formula but it didn't work.

    =IF(G1:G53=53017,SUM(E2:E58))

    Can anyone advise?

    Thanks!

    1. Hi Lisa,

      You can use the formula similar to this:
      =SUMIF(G1:G53, 53017, E1:E53)

  45. I need help on an Excel formula. I have A group of cells containing text. The text in the range is either "In Process" or "Completed". If any of the cells in the range have text "In Process" the lead cell should return "In Process", if not, it should return "Completed". Please assist.

  46. Hi, how do I get the value if true to be another formula? I have a list of customers (column A), and a list of corresponding dates (column B), I want to set the value if true to be, If cell A1 = Customer x, out put to be date (b1) + 2 days.
    By having the formula as =IF(A1=Customer,"B1+2","") the output is B1+2 whereas I want it to display the date in B1 + 2 days?
    Thansk

    1. Hi Chris,

      Just remove the quotes enclosing B1+2 because they turn any expression into a text string, and apply the Date format to the cell containing the formula:
      =IF(A1="customer",B1+2,"")

  47. In Cell L92 I am try to tally Cells F8, F12, F16, F20 if they say Yes. How do I do this?

  48. Svetlana, why you ignore email from my question?

  49. Can you please help me with following formula

    i have a calculation in Cell A and another calculation in Cell B

    in another cell i want to determine results as following

    if value in cell A and Cell B are greater than or equal to .5 give me true otherwise false

    if values in cell B is blank take value from cell A only and provide results

    1. Hi Kamran,

      You can use a formula similar to this:

      =IF(AND(A1>0.5, B1>0.5), TRUE, IF(AND(A1>=0.5, B1=""), TRUE, FALSE))

  50. Hi Svetlana

    I am looking out for a formula to calculate an incentive structure

    If My CTC is 10000 , and i have given a profit of 45000 , I want an formula to calculate the following slabs in incremental basis.

    If employee achieved 3 times of ctc 5 % incentive
    If employee achieved 3 to 4 times of ctc 10% incentive If employee achieved 4 to 5 times of ctc 15% incentive
    5 to 6 times 20%
    6 to 7 times 25%
    Above 7 times 30 %

    45000 split in 3 slabs

    30000 = 30000 * 5 % Eligibility on 30000
    30000 - 40000 = 10000 * 10 % Eligibility on 10000
    40000 - 45000 = 5000 * 15 % Eligibility on 5000

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