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

  1. I didn't make my example ,clear. I have column "A" with these numbers 5 4 2 3 6 7 - In column "B" I have these numbers 30.92 , 31.29 , 31.11 ,31.17, 31.29, 31.07 If i select a number in column A or an adjacent number I want the minimum corresponding number in column "B" I need a formula for this. I tried this formula but all i get is zero.=MIN(IF(A4:A9>4,A4:A9<8,B4:B9)). i used control, shift, enter.I added some commas between some of the numbers to make it clearer.

  2. Hi All,

    I am looking for assistance in developing one syntax where there could be three different conditions. Those conditions could be -

    1. If Date Is not crossed then Display date
    2. If Date Is crossed and status is not updated then "Planned Date Crossed, No status updated"
    3. If Date Is crossed and status is updated then Variance number between Plan Vs Actual Date.

    Excel have to verify certain details in the table before these three conditions could be verified. So one may have to use index match formula for the same.

    Also, the single cell number format should change automatically based on the cell value which could be date (First Criteria), numeric (Second Criteria) and text (Third Criteria).

    It looks pretty tough to me. Can anyone help on the same.

    Best

  3. Hi there,

    Can help me on this question.

    Cell B Cell C Cell D
    Point Eastings Northings Row 2
    1 567768 6376890
    2 567702 6376035
    3 567835 6377836
    4 567936 6376471
    5 568035 6376044
    6 566834 6377837
    7 567413 6378635
    8 566036 6378999
    9 568634 6377941
    10 566345 6376534

    1st Point
    2nd Point
    Diff East
    Diff North
    Distance

    I'm trying to use vlookup to calculate the distance between two points.
    I need to be able to choose any point I wish and type that into cell D14 (starting point). Then the same thing for the end
    point in cell D15. The difference between eastings and northings of both slected point will be display in cell D16 and D17.
    The distance (=SQRT(DiffEast^2 + DiffNorth^2) between two points will be displayed in D18.

  4. hii

  5. Hi,

    I am trying to use the if function but I am trying to use it for 3 different results
    if a number is lower than 18 if should say approved
    if it is between 18 and 24 it should say approaching
    and if the number is larger than 24 it should say finished

    I was able to use this formula like this
    =if(A2<18, "approved", "") that is what I am using but the problem is that the numbers contantly change and I need for it to include the 3 posibilities but this formula does nto allow me.

    Please help

  6. Hi!

    I've been identifying non-integer numbers on large datasets using this formula (cell F2 for example):
    =IF(INT(F2)=F2, 0, 1)
    It works great...until you drag the function past 44,000 or so rows, and then it stops working.

    Any suggestions why or a workaround?

    Thanks in advance!

  7. I am working on putting my budget on excel, and not sure how to have adding the amounts as they are enter to the correct category.
    =IF( EXACT(F6:F200,"Car Insurance"),0,SUM(G6:G200))
    what I am trying to accomplish here is that when i choose my category on F6 TO F200 from a drop down list, Car Insurance is an example, but i have electricity, cell phone, groceries, etc. After i choose my category next to it n G6 TO G200 I enter the expense amount for that day for that specific category. I want to create an statement that say if f6=ELECTRICITY, then use add G8 put it on another cell, b6, etc, if f6=cell phone, then add G6 to b8, etc.

  8. Trying to determine the lowest rating for a given security with two different ratings. If A1=Baa1 and B1=A3, the formula should return a value of Baa1 given the following hierarchy of ratings from highest to lowest (Aaa, Aa1, Aa2, Aa3, A1, A3, A3, Baa1, Baa2, Baa3, etc.). Thanks.

  9. plz help me...
    1000000< marks 1, other every Lilian 1 marks and full marks 06

  10. Mine seems simple. I need

    Formula in H10
    If C10 contains "Payment" Then enter the sum that's in G10 into H10
    So if it's a payment I need the sum to move right one cell
    and yet I cant figure it out.

    Thanks

    1. I DID IT!

      =IF(C10="Payment",G10,"0")

  11. I have an interesting question and I am not sure if it is even possible.

    Can this be done? IF cell A1 = PR Then INCLUDE a 0 in Cell B1

    What I am trying to accomplish is adding a 0 to a zipcode. For example the state PR has zipcodes that begin with a 0 so if the cell has the text PR then INCLUDE a 0 to the cell that contains the zipcode.

    Is this possible?

  12. I am looking to join two cells together but separate the two values by a comma into a new cell.Can someone supply an =if statement that would help for this?

    1. Hi there! I do this often to combine two cells with only a space separating and it's super easy. You simply add a new column and within that new column, you type in "=G2&","&H2" . The G2 & H2 simply represent the cells you want to combine. What you have in between the quotations is what is entered in between the combination, etc. Then do a drill down down the column for all others and you're all set!

  13. Hi,
    I'm Looking IF Formula For My Expense Sheet For Example In B1 I Write Anything Accept Leave Of Day Off So In C1 I Need Number 200 & When In C1 I Get 200 So In D1 Automatic I Get 250.

    Looking for Your Reply.

  14. Sir/Madam,
    While awarding grades to particular mark range, I want a '-' where the marks are zero . I applied this formula but it doesn't seem to work.

    =IF(BX9>90,"A1",IF(BX9>80,"A2",IF(BX9>70,"B1",IF(BX9>60,"B2",IF(BX9>50,"C1",IF(BX9>40,"C2",IF(BX9>32,"D",IF(BX9<=32,"E",IF(BX9=0,"-",IF(I8="AB","AB"," "))))))))))

    Kindly guide me.
    Thanks in advance .
    Malar

    1. Dear Malar as I have understood IF function it has three parts. Logic (greater, equal, less than etc), true or false parts. What you are trying to do is to put 10 IF functions in one cell. So I think it will not work. If you want to put "-" where marks are zero use this
      IF(BX9=0, "-", "")
      if BX 9 is zero it will return - value in cell other wise it will be empty.
      or use IF(BX9=0, "-", BX9) it will return original value if its not zero.
      As I understood you what you are trying to do is not possible with IF function. You have to find other function for this.

  15. If values in A1:A10 are in numbers and values in B1:B10 are alphabets. some of the values are empty between B1:B10 like B3, B4, B7, B8. how to calculate values of A3, A4, A7, A8 with formula.

  16. I am trying to add a column of money if the number opposite = the number 50

    =IF((J44:J55=50,sum(I44:I56),0)) - but this doesn't work

    Tks in advance.

    1. =SUMIF((J44:J55,"50",I44:I56)

  17. I am trying to add a column of money if the number opposite = the number 50

    =IF((J44:J55=50,sum(I44:I56),0)) - but this doesn't work

  18. Am working on a commission for my staff and I need an IF CONDITION OR VLOOK FORMULA to make it easier for me. This is how I have allotted the commission
    Sales Commission
    20,000.00 is 50.00
    25,0000 is 100.00
    30,000.00 is 150.00
    35,000.00 is 200.00
    40,000.00 is 250.00
    Please kindly help me out

  19. HI IM TRIED FROMULA FOR IF TWO COLUMN VALUE IS SAME THEN I WANT PRINT VALUE TO ANOTHER COLUMN
    eg:-( if a=b value is same then c value print in e column)
    note** i want column value not cell like a2 value in some time is a4 value so can tell me the fromula please find the value of two column and then print value )

  20. Hello,

    I have 2 columns..... say A & B

    Assume column A1 has some values already, and If I type any other words other than "Approved" in B1 or Column B1 is left empty, then Column A1 should stay in same old value, not even change to FALSE.....

    If and only if I type "Approved" in Column B1, then A1 should automatically change to "COMPLETED"

    These formula i need to populate throughout the column B

    Thanks....

  21. Hi

    Can anyone help me on this? I am creating a formula for time and date. If the time exceed at 5:00 PM. The date and time will appear the next day and the time will be 9:00 AM.

    Example

    8/1/2017 5:00 PM result 8/1/2017 5:00
    8/1/2017 5:02 PM result 8/2/2017 9:02

    What is formula for this?

    1. Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
      Info:
      9AM is 09:00 ( 9/24)
      5PM is 17:00 (17/24)
      From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
      #1 After 5PM show Tomorrow 9AM
      IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
      #2 After 17:xx show Tomorrow 09:xx
      IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
      # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

  22. Helloo, anyone would like to help me,
    how use "if function. in conditional.
    this is the situation.
    if the employee paid 100 per month and and if he employed for less that 5 years,this would be his computation for separation fee, 100/2=50/yr, otherwise if he emplyod for more than 5 yrs this would be the computation., 100/ yr.

    what is the best formula for this one.

  23. Hello!

    I have a formula I wanted to write but I am not to sure what to use.

    So here is the idea:

    On sheet 1 I have A1 with a value and in B1 I want a formula that finds the same data in A1 on Sheet 2 A:A and return the value that is in the matching A:A cell with the value that is in the next cell over (B).

    I dont know if that makes sense.

  24. =IF(AND(C9="abc", E9=TRUE), C3, IF(AND(C9="abc", E9=FALSE), D3,""))

  25. Could you please help?
    I need a warning when transposing numbers in different columns using IF function. Trying to check bank balance in (A1) with other itemised columns, only one value per row.have tried =IF((B1,C1,D1,E1,)=A1,"OK","NG") Have received #value results at times. Cheers Garry

    1. Hello, Garry. Thank you for contacting us.

      If I understand your task correctly, the formula below should work for you:

      =IF(OR(B1=A1, OR(C1=A1, OR(D1=A1, OR(E1=A1)))), "OK", "NG")

      If the task is different, please describe it in more detail. I'll do my best to help you.

  26. Hi everybody,

    Can you please help me work out on this formula:

    I have the following table of an employee and I need to add 20 leaves on the
    anniversary date of joining of this employee i.e. every year on the 20th of June in his/her credit:

    Date of Joining: 20th June 2014 (dd/mm/yyy) 20/06/2014

    Duty Period Add 20 Leave Taken
    20/06/2015 25/07/2016 20 26/07/2016 30/07/2016
    31/07/2016 31/05/2017 0 - -
    01/06/2017 15/06/2017 20 16/06/2017 15/07/2017

    Column A1&B1 is the Duty period, C1 Add 20 or leave it blank, D1&E1 is the leave taken period. Row 1 shows Duty period and leave taken,Row 2 shows only duty period and since duty period does not fall in the range of anniversary date C1 will shows "0" or blank. In the 3rd row the anniversary date falls in the leave period so 20 is added.

    I have searched the whole of Internet but yet to find a solution for the same. A formula for the same will be much appreciated.

    Thanks in advance.

    John Sanil

  27. i want to know what will be the formula if i want to compute for example:

    value for A=P, B=P, and C=P, then it should be S
    and vice versa;

    and;

    value for A=P, B=P, and C=NP, then it should be S

    value for A=NP, B=NP, and C=P, then it should be

    or: if for the cell a,b,and c have at least more than 1P then it should be P; same with the NP if more than 1 then it should be NS

  28. How to create a IF formula for Cell that contains percentage between 95% - 100% will be stated as "ACTUAL", less then 95% will be stated as "DOUBLE", between 100% - 115% will be stated as "FIFTY PERCENT", above 115% will be stated as "NIL"

    1. Hi Virendra,

      Copy the following following formula in B2 (the answer cell):

      =(IF(A2>114.9%,"NIL",IF(AND(A2>=100%,A2=95%,A2<=100%),"ACTUAL",IF(A2<95%,"DOUBLE")))))

      Format cell A2 as percentage.

      The premise:
      94.9% and below = DOUBLE
      95% to 99.9% = ACTUAL
      100% to 99.9% = FIFTY PERCENT
      115% and above = NIL

      Format cell A2 as percentage.

      The only problem with this formula is that if nothing is mentioned in cell A2 the answer always displays "DOUBLE".

      Hope Svetlana Cheusheva or someone can improve upon the formula.

      Regards,
      John Sanil

      1. Dear Virendra
        Sorry the premise (100% to 99.9%)
        should read as: 100% to 114.9%.

        Rest all remain the same.

        John

  29. Can someone help with the formula as per below mentioned details

    If "C" column Budgeted figure is 40.83 & Actual in "D" column is 37.69 then the VAR in "E" column is -3.15 with 92% Achievement in "F" column and the weightage defined for 100% achievement is 35 Marks which is in "G" column.

    I am looking out the IF formula help that if "F" column is less then 79.99% then "0" marks, and if same is between 80% to 94.99% then half of the 100% weightage defined 35 marks that is 17.5, and if the scoring is between 95% to 100% then the Actual % of weightage defined like if 97% achieved then 97% of 35 marks that is 33.95 marks.

  30. Hi,
    please, i have strings in a cell "itdr_a0863_tempodisconti-rtg_it_tem_mth_300_bask" and i would just like to get the "bask" word what function will i use or what is the solution. Thanks

  31. Need help with correct formula for:

    Parcels less than 2kg in weight are charged at 0.365cents per gram
    Parcels over or equal to 2kg in weight are charged at 0.425cents per gram

    Thank you.

    1. Hello, Marie. Thank you for your question.

      Let's suppose that the parcels' weight is in A1.
      You put the following formula into B1 in order to get the charge for the parcel:

      =IF(A1<2, "0.365 cents per gram", IF(A1>=2, "0.425 cents per gram"))

      I hope this helps. Please let me know if you have any other questions or difficulties.

  32. P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/20/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00

    I have this data in sheet1 whereas i have, the below data in sheet 2

    4/19/2016 0:00 4/20/2016 0:00 4/23/2016 0:00 4/24/2016 0:00
    P001

    i want a formula which can highlight corresponding cell in sheet 2 if the date against P001 occurs in sheet1.

    1. can anybody help pls.

  33. How to display, if( c2= a(alphabet) or b or c or d or e or f or g, the value is true , or value is false). Please help

    1. Hello,
      You can try the following formula:

      =IF(OR(C4="A", C4="B", C4="C", C4="D", C4="E", C4="F", C4="G"),TRUE, FALSE)

      In case you need the whole alphabet, use this one:

      =IF(OR(C2="A", C2="B", C2="C", C2="D",C2="E", C2="F", C2="G", C2="H", C2="I", C2="K", C2="L", C2="M", C2="N", C2="O", C2="P", C2="Q", C2="R", C2="S", C2="T", C2="V", C2="X", C2="Y", C2="Z"),TRUE, FALSE)

      I hope this helps. Please let me know if you have any other questions or difficulties.

  34. =IF(W7260$U$7276,"Late")))

  35. Good day

    Please can you assist me with my nested IF formula:

    =IF(W7260$U$7276,"Late")))

    If Cell reference W7260 is blank - I want to return a blank, how do accomplish this?

    Thank you

  36. Hi, Need desperate help with a formula.

    If column (J6:J21)contains the value (0.8) and if amount in column (K6:K21)is equal to or greater than 192 then cell turns yellow, or equal to or greater than 256 cell turns green, or equal to or greater than 320 cell then turns red.

    The same for if column (J6:J21)contains the value (0.5)equal to or greater than 120 then cell turns yellow, or equal to or greater than 160 cell turns green, or equal to or greater than 200 cell then turns red.

    Thankyou in advance for your help

  37. I'm tracking selling stuff and the site I'm using takes 20% commission for any item I sell above $14, anything $14 and under they take a $2.95 flat fee. To calculate this I'm using the equation:
    =IF(A3>14,A3*0.8,A3-2.95)

    However, since I'm putting this on things that haven't been sold yet, I only want there to be an output number if the item actually sold, otherwise I want it to be zero so I can add up the entire column without having some cells be "-2.95". The A3 cell in this example is blank because it hasn't sold yet. How would I do this?

  38. pl. help me with correct formula.

    example:

    =if(A1="apple",B1*4%),if(A1="animal",B1=10%),if(A1="tiger",B1*20%))

    pl. share correct formula to get the result,

    Thanks in advance

  39. Hi!
    Great website. I'm learning a good bit. Thanks.

    I'm trying to configure a simple budget. I have two columns: one is a category (e.g., "Groceries"). The second is the amount purchased.

    I want to Sum all "Groceries" formula. How would I do this?

    It would start like this...

    =IF(C2="Groceries",

    Thanks!
    Daniel

    1. Hello, Daniel. Thank you for your question.

      You can use the SUMIF function to configure your budget. Let's suppose that you have a table with types of your costs in column A and the amount purchased in column B. Please put the word "Groceries" in cell D2 and following formula in E2:

      =SUMIF(A2:B8, D2, B2:B8)

      You can learn more about this function in our blog article. I hope you'll find this information helpful.

      I hope this helps. Please let me know if you have any other questions. I'll be happy to help!

    2. you can use formula =sumif

  40. I need help - If a cell is $350,000 or less then I want another cell to come out to 0 - d6 is $350,000 then I want J15 to have 0 entered. Whereas, if I want D6 greater than $350,000 then I want J15 have $595.00 entered. I cannot figure this out at all. Any help would be appreciated. Thanks a bunch!

  41. I am trying to write a formula where 'x'=1 in 1 cell, then 'x'=0 in the 2nd cell, then 'x'=0.5 in the 3rd cell. (ie B6, C6, D6), and then I need (ie E6) to be the sum of B6,C6,D6.

    I am in desperate need of help. Please help!

  42. Inv. No. Cust Code
    390100209 800412
    390100210 800808
    390100211 801007
    390100212 801392
    390100213 801395
    390100214 801405
    390100215 801412
    390100216 801547
    390100217 802697
    390100218 802775
    390100219 802836
    390100220 803033
    390100221 803346
    390100222 800733
    390100223 800121
    390100224 800527
    390100225 800558
    390100226 800577
    390100227 800702
    390100228 800702
    390100229 800702
    390100230 800702
    390100231 800702

  43. I am trying to build a price list based on tiers. If C2 is less than 20, multiple it by two, if 20 to 99 multiple by 1.54%, if 99 to 599 multiple by 1.43, if 599 or more multiple by 1.34. Also C2 needs to have a tax rate of .08517 added to it but before commission markup.
    Ex: $20 x .08517 = 1.71 $20 x 2 = $40 plus $1.71 tax = $41.71.
    I keep getting errors.

    1. Hello, June,

      as for the first part of your task, you can use this nested IF:
      =IF(C2<20,C2*(1+2%),IF(AND(C2>=20,C2<99),C2*(1+1.54%),IF(AND(C2>=99,C2<599),C2*(1+1.43%),IF(C2>=599,C2*(1+1.34%),""))))

      As for the second part, could you please describe it in more details? Thanks

  44. I want to convert x into an absolute number. The value of "x" has to be 0 to 5. I have 3 different rows, (ie B6, C6 and D6). I then need total sum of these rows ie in E6.
    I have this formula so far, =IF(AND(ISBLANK(B4)=FALSE,B4>=0),IF(AND(ISBLANK(C4)=FALSE,CB42>=0),IF(AND(ISBLANK(D4)=FALSE,D4>=0),B4+C4+D4))) but I cannot figure out how to include the x=a number 0-5.
    Please help.

  45. Hi,

    I want to convert KB values and MB values into GB's, could you please help me in this .

    cells are filled like below
    eg : 19K, 1.0M
    I want to convert them into GB's

  46. hi i need vb code for
    if my value of sheet 1 "B" column value is same as sheet 2 "B" column value is correct and then sheet 1 "k"column value print in sheet 2 "K" column if anybody know please let me know

    1. Hi, Irfan,

      I'm sorry, we don't create VBA codes. Also, I'm afraid that your task is not entirely clear.
      I kindly suggest you to ask around MrExcel forum for the vba code you're looking for. Make sure to explain your task in more details.
      Thanks!

      1. THANK YOU NATALIA FOR YOUR RESPONSE

  47. hi i need vb code for
    if my value of sheet 1 b2 is same as sheet 2 b2 value is correct and then sheet 1 k2 value print in sheet k2 if anybody know please let me know

  48. I am trying to figure out what to do with VBA code and to get some kind of conditional function in Excel, but it doesn't seems to work.
    I have a list of articles in one column and a list of numbers in another column. What I want is to get the number display in the cell in third column but conditionally. Example: if A2=B2 then in the cell C2 I want the number which is in D2. But cells in A column are all set up as validations from E column. If something is not clear let me know please.
    Thank you!

  49. if(B5>20,B5<50,"20-50") How to work, please find like this

    1. Hello

      Try the following formula:
      =IF((AND(B5 > 20, B5 < 50)), "20-50", " " )

      You can also read more about IF formulas with multiple conditions here

      Feel free to contact us if you have any other questions.

      1. Hi,
        Please correct this,
        =if(A<B,B-A,IF(B=0,0,""))

  50. Great work and service to us Excel noobs :-)

    I have multiple sheets and my main dashboard is assigning a "word" rating based on the value from different sheets (from 0 to 1). But it is broken and I am at a loss - any help?

    =IF('sheet #1'!S21=",",IF(<=.20,"Not Effective",IF(<=.40,"Needs Improvement",IF(<=.60,"Generally Effective",IF(<=.80,"Effective",IF(<=1,Strong,"Error"))))))

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