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

  1. Hi,
    I have one question.
    Actually i just want to create a new cell if my "if Condition" become true and not to create any cell if condition become false.
    Can you please suggest is there any way to resolve with this issue ?

    Thanks in advance.

    1. You may have to learn VBA for this.

  2. Hi,

    Is there an 'If Statement' formula for a pie chart that will say ...
    'If A4 = 0 dont include, if A4 = >1 include' ??

    Thanks

  3. B6 is 70% And D19 is 0 i need the result is Needs Improvement.

    Now I'm Trying with this one which is not getting the result.

    IF(AND(B6>=70%,D19=0,"Needs improvement".

    Please help me.

    1. =IF(AND(B6>=70%,D19=0),"Needs improvement","")

  4. B6 is >70% And D19 is 0 i need the result is Needs Improvement.

    Now I'm Trying with this one which is not getting the result.

    IF(AND(B6>=70%,D19=0,"Needs improvement".

    Please help me.

  5. Hi,

    I would like to create a formula for a spreadsheet.

    If D4 = 1, then F4 = $15
    If D4 = 2, then F4 = $20
    If D4 = 3, then F4 = $25
    If D4 = 4, then F4 = $30
    If D4 = 5, then F4 = $35
    If D4 = 6, then F4 = $40

    etc.. all the way to D4=10, then F4=$60 ($5 increments).

    Essentially, all I would like to do is enter the number '5' into D4 and the appropriate total cost will automatically appear in F4.

    Thanks in advance!

    1. Hi,
      Type this into F4,

      =D4*5+10

      And format it as currency ($)

      Using If loops make it messy. If you must use If, use the following,
      =IF(D4=10,60,IF(D4=9,55,IF(D4=8,50,IF(D4=7,45,IF(D4=6,40,IF(D4=5,35,IF(D4=4,30,IF(D4=3,25,IF(D4=2,20,IF(D4=1,15,"Enter a correct value"))))))))))

  6. Kindly prepare a formula or correct this formula

    If a man bought x, he is entitled for 0.8*the price
    If a man bought y, he is entitled for 0.8*the price if 0.8 *the price < 100 otherwise he will be paid 100

    If a man bought z, he is entitled for 0.8*the price if 0.8 *the price < 1500 otherwise he will be paid 1500

    =+IF(G2=0,0.8*L2,(IF(AND(G2=1,L2*0.8<100),0.8*L2,100,(IF(AND((G2=2),(L2*0.8<1500)),0.8*L2,1500,(IF(G2=3,L2,L2)))))))

  7. I would like to create a formula by if

    check value of a cell A2 = 1,
    if true its will select a already written value from a cell H2,
    if false we wants go for another checking A2 = 2,
    if second checking is true its will select a already written value from a cell H2,
    if second checking is false we wants go for another checking A2 = 3
    if Third checking is true its will select a already written value from a cell H3
    if Third checking is false we wants go for another checking A2 = 4

    its will a continuous checking in each cell. is it possible in excel by "IF" function ?

  8. Hi,
    I would like to create an "if and" formula where: if the value in h183 is 4 and the value in a183 is 1, then the value in i183 is 2. It seems simple but I can not get the syntax correct. I have tried
    =if(AND(H183=4,A183=1)),[I183=2]
    what am I doing incorrectly.
    Thank you

    1. Hi!

      You were almost there :)

      Try to enter the following formula in I183:

      =IF(AND(H183=4,A183=1), 2, "")

  9. i want to know formulae for example that DHL, FEDEX, ARAMAX HAS DIFFERENT FUEL SURCHARGE LIKE 14.50%, 15.50% AND 13.50%....SO HOW CAN WE FILL ONE CELL FEDEX AND GET VALUE ON ANOTHER CELL 15.50%

    PLS HELP ME

  10. What will be the formula of if for multiple reason. Such as from USD 0 to 200,000 tax will be 1%, 200,001 to 500,000 will be 2.5%, 500,001 to 1500,000 will be 3.5%.

  11. How does one combine the 4 IF Formula in one cell? I'm able to combine 2 together with no issues but when trying to combine 3 or all its fails.

    1. If A1 is blank (True) then D1 is blank
    =IF(ISBLANK(A2)=TRUE,””)

    2. If A1 is not blank and B1 and C1is blank then D1 = A
    =IF(ISBLANK(A8)=FALSE,IF(ISBLANK(B8)=TRUE,IF(ISBLANK(C8)=TRUE,"A")))

    3. If A1 is not blank and B1 is not blank and C1 is Blank (True) then D14 = B
    =IF(ISBLANK(A14)=FALSE,IF(ISBLANK(B14)=FALSE,IF(ISBLANK(C14)=TRUE,"B")))

    4. If A1 is not blank (False) and B1 is blank (True) and C1 (False) is not blank then D19 = C
    =IF(ISBLANK(A19)=FALSE,IF(ISBLANK(B19)=TRUE,IF(ISBLANK(C19)=FALSE,"C")))

    Formula 1 and 2 together.
    =IF(ISBLANK(A1)=TRUE,"",IF(ISBLANK(A1)=FALSE,IF(ISBLANK(B1)=TRUE,IF(ISBLANK(C1)=TRUE,"A"))))

    1. Hi TinWin,

      Try the following formula:

      =IF(ISBLANK(A2), "", IF(AND(ISBLANK(B2), ISBLANK(C2)), "A", IF(ISBLANK(C2), "B", IF(ISBLANK(B2),"C"))))

      1. Wow you rock Svetlana! That totally worked!

  12. I having trouble getting a if statement to perform a formula if the condition applies but to do nothing if it doesn't.
    here is my sample
    I need the number of characters in a cell to be 20 or less, so I want to reduce the number using this formula =IF(LEN(A1>=20),LEFT(A1,LEN(A1)-10))
    which works just fine, but i want it to do just copy the value in the the original cell if the length is already less than 20.
    What do i have to add to this formula?

  13. hello...........

    how can use product wise and date wise formula in excel...
    as a example..........
    1st march to 15th march my cell phone price 1550/-
    then price increase product value 16march- 31st march - 1575/-
    so suggest me ???
    how to apply condition in excel??????

    Thanking you
    Amit Gaurh

  14. Hello,
    I have a spread sheet with a Header in D1 (Issue date). I am trying to get result in Column F (Review) of "Not Due" if the date is less than 640 days from issue, "Due" if the date is between 641 to 720 days from issue, and finally "Over Due" if it is greater than 721 days from issue. I have been trying the IF function and can only seem to get 2 returns but not the third. Thanking you in advance.
    David.

    1. I think I've got it;
      =IF(OR(D2=""),"",IF(D2>=TODAY()-641,"Not Due",IF(D2>=TODAY()-721,"Needs Review","Over Due")))
      I have then applied Conditional formatting so that Not Due = Green, Needs Review = Yellow, and Over Due = Red
      Seems to work Ok.

  15. a
    r1 12,00
    r2 34,00
    r3 78,00

    if the latter is my workbook is it correct to type:
    =IF(30>A1>10;1;IF(70>A1>30;2;IF(A1>70;3))),
    in order to receive the number 1 for the specific condition described or the numbers 2 or for the other conditions described accordingly?
    I think the formula is right but excel unfortunatelly gives results of 1 only (cannot calculate 2 or 3).
    to specify, this is the worksheet after the formulas copied to the b column:

    a b
    r1 12,00 1 ok formula is:=IF(30>A1>10;1;IF(70>A1>30;2;IF(A1>70;3)))
    r2 34,00 1 ??? formula is:=IF(30>A2>10;1;IF(70>A2>30;2;IF(A2>70;3)))
    r3 78,00 1 ??? formula is:=IF(30>A3>10;1;IF(70>A3>30;2;IF(A3>70;3)))

  16. i have 2 sheets one having blanks for all cels in al rows and second sheet having data as 0 and 1 for same cels and rows, i need to merge the data in the third sheet or u can say i need to copy the data in 3rd sheet by Usinf IF formula. Please suggest

  17. Hi Svetlana,

    I am very new to excel and am trying to write an if function that if
    cell D12 = OH multiple cell H38 by 8.25% if
    cell D12 = MI multiple cell H38 by 8.75% otherwise zero

    Thank you for your help

    1. Hello Mary,

      Here you go:

      =IF(D12="OH", H38*8.25%, IF(D12="MI", H38*8.75%, 0))

  18. I want to add If formula to a formula that is calculating dates.
    Current formula in cell: =E2-N2
    Result: 2/12/2015
    I want to add a IF Formula to state= if today (Today() ) or less leave cell blank. If not, keep the date.

  19. Good Afternoon,

    I am trying to sort out how much paid and voluntary work I do for a charity, what I need to know how many hours I have done paid.

    So if it says paid in one cell I need it to add the hours to another cell. Is this doable?

    Thank You

    1. Hello Cody,

      You can use a SUMIF formula similar to this:

      =SUMIF(B:B, "paid", A:A )

      Where column C contains hours, and column B - work descriptions like "paid".

  20. Hi,

    I'm trying to figure out a formula, where if A is blank than leave destination cell blank, if A contains a date then destination cell to show the month from that date.
    I came up with the following, which pulls the months through, but doesn't leave the cell blank if A does't have a date.

    =IF(ISBLANK(A),"",MONTH(A:A))

    would really appreciate your help.
    thanks,
    Suzie

    1. Hi Suzie,

      Because the IF function works on a cell level, you should specify a cell reference in a formula. For example, enter the following formula in row 1, and then coy it down to other cells in the column:

      =IF(ISBLANK(A1),"",MONTH(A1))

  21. a
    r1 12,00
    r2 34,00
    r3 78,00

    if the latter is my workbook is it correct to type:
    =IF(30>A1>10;1;IF(70>A1>30;2;IF(A1>70;3))),
    in order to receive the number 1 for the specific condition described or the numbers 2 or for the other conditions described accordingly?
    I think the formula is right but excel unfortunatelly gives results of 1 only (cannot calculate 2 or 3).
    to specify, this is the worksheet after the formulas copied to the b column:

    a b
    r1 12,00 1 ok formula is:=IF(30>A1>10;1;IF(70>A1>30;2;IF(A1>70;3)))
    r2 34,00 1 ??? formula is:=IF(30>A2>10;1;IF(70>A2>30;2;IF(A2>70;3)))
    r3 78,00 1 ??? formula is:=IF(30>A3>10;1;IF(70>A3>30;2;IF(A3>70;3)))

  22. I am looking for a formula that allows me to see if I have one 1, one 2, one 3, one P, one SS, one LF, one, RF, one CF, and three X each column. If I have two of one of the numbers or zero of one of the numbers then I want it to be false. If I have it meets my criteria then I want it to be true. I hope that makes sense.

  23. Thank you very much.
    I have had to use this formula to create an extra column to do what I could not with a formula. Is it possible to get a total to count the two columns on one row and just add in to total rather than having to use the above formula in a column of its own. E.G the total will need to represent any time that there is date in both Column A and column B but does not need to show any data when there is data in only one of the columns. I would also like this represented by a number rather than a word.

    Is that possible ?

    1. Sean,

      If my understanding is correct, you want to count the total of rows that have any value both in column A and B. If so, you can use the following COUNTIFS function:
      =COUNTIFS(A:A, "<>"&"", B:B, "<>"&"")

      If you are looking for something different, please clarify.

  24. Hi Svetlana and team,

    I would like to use a COUNTIF function or any other function to do this. If A5 and F5 contain values then K5 = Yes or 1 eg.
    Is this possible within excel ?

    Thanks

    Sean

    1. Hi Sean,

      Sure, here's the formula for K5:

      =IF(AND(A5<>"", F5<>""), "Yes", "")

  25. Hi there,
    i would ask for help for the following:-

    cell c36= if L15 until L32 is SR, than total up k15 until k32 that contain SR in L15 until L32.

    cell c37= if L15 until L32 is ZR, than total up k15 until k32 that contain ZR in L15 until L32.

    what i want is, if there is SR in the L row, than total up.
    if there is ZR in the L row, than total up too , but i want 2 different value. Please help

  26. IF(E3:E281=71,5375,IF(E3:E281=12,6105,IF(E3:E281=13,6555,)))
    how can i write IF(E3:E281=7(i),5375,IF(E3:E281=1(ii),6105,IF(E3:E281=1(iii),6555,)))

  27. "In cell G2, create a formula using the IF function and structured references to create a calculated column that determines the value of Weeks Vacation based on the following criteria:
    a. If the years employed are less than 3, the Weeks Vacation should be 2
    b. If the years employed are 3 or more, the Weeks Vacation should be 3
    If the formula in cell G2 does not fill the table column, fill the remaining cells with the formula you just created."

    How would i do this formula?
    help please?!?!?

  28. Hi,

    I want my formula for Ks to return value as blank or "". My formula orginally is:
    =INDEX($D$5:$D$8,SUMPRODUCT(--($E$5=B5:B8),ROW($A$1:$A$4)))

    The formula above works fine as it returns a value when i put value of any number in the column E5. However, I want my formula to return blank when a non-value, ie: a text or value with percentage such as '10%.

    I tried this:
    =IF(AND((INDEX($O$21:$O$24,SUMPRODUCT(--(G36=$M$21:$M$24),ROW($A$1:$A$4))))),"---")

    but it returns "----" regardless when the criteria is met.

    Below is example of excel, copy paste in a blank sheet.

    FOR SOIL
    Coefficient for shaft resistance, Ks
    SPT N example
    IN TEXT Range 1 Range 2 Ks SPT (IN VALUE) OR RQD (%-TEXT)
    N<10 0 10 3.5 3
    11<N<50 11 50 3 10%
    51<N<100 51 100 2.5
    101<N<150 101 150 2

    Result to get:
    FOR SPT 3 GET Ks 3.5 (ok- formula 1 works fine)
    FOR RQD 10% GET Ks #VALUE! (not ok- formula 2 -because when we
    insert a non-number it should return blank cell.

    Thanks in advance,
    Aya2016

  29. Column A1=Mango,A2=Tomato,A3=Chicken I want in column B1 mango=fruit,B2 Tomato=vegitable and B3 Chicken=meat.How to make function.Pls help

    1. Hello Manu!
      You can use the following formula:
      =IF(OR(A2="Mango",A2="Apple"),"fruit",IF(OR(A2="Tomato",A2="Potatoes"), "vegitable",IF(OR(A2="Chicken",A2="Beef"),"meat","")))

  30. I have a list of data in a column, it might be data or empty cell. I wanted to gather that data in a single line separated with commas and & sign before the last data. Basically i am getting this data from user choice.
    Thanks for helping.

    1. I have tried using Concatenate but i am getting something like; (1 inverter, 2 battery,,,,, 2 bulb,,, and so on). I don't want these useless commas and want a "&" sign.

  31. HI

    How can i form this formula...

    I want to compute the BMI of children with regards to their (Age, Gender, Weight, Height)? thanks

    1. what function should i use?

      B1=Gender C1=Age D1=Weight E1=Height F1=BMI G1=COMMENT (underweight,normal, overweight)

      IF(B2=F/FEMALE, F2>14, "underweight", F224"Overweight"), IF(B2=M/MALE, F2>15, "underweight", F2<=25, "normal", F2<25, "overweight")

      please how will i correct this?

      thank you

  32. A1 is 7300
    B2 is 7850

    in C2 row = if a1 is less than b2= 0 and if not than a1-b2

    request suggest the formula on my email

  33. Hello,

    You can use the following nested IF formula that lists all conditions:
    =IF(F1>0,F1&G1,IF(E1>0,E1&G1,IF(D1>0,D1&G1,IF(C1>0,C1&G1,IF(B1>0,B1&G1,IF(A1>0,A1&G1,""))))))

    1. Anderi,

      It worked but it's showing the results in one columns. Can you tell me how to show the results in two separate columns?

      Thank you!

    2. Thank you so much for your help Andrei Bertosh! It worked!!!

  34. Anyone willing to accept this challenge and help me?

    I have data in 7 columns, 5000 plus rows. First 6 columns have numbers they refer to the value in column 7th which is text. Example

    10 0 0 0 0 0 H
    10 101 0 0 0 0 S
    10 101 10105 0 0 0 T
    10 101 10105 10 0 0 W
    10 101 10105 20 0 0 F

    I want to see the result in the new sheet like this.
    10 H
    101 S
    10105 T
    10 W
    20 F
    Column 5th and 6th value is "0" so the result should be "no value"

    Thank you for your help in advance.

  35. Can someone please help with,If A1>=10; Efficient, If A1=5; Average, If A1=3; Poor, If A1<3 ;"" (blank).

    Thank you.

    1. Hello,

      You can use the following nested IF formula that lists all conditions:
      =IF(A1>=10,"Efficient",IF(A1=5,"Average",IF(A1=3, "Poor","")))

      I hope this helps.

      1. Thanks! For me work grate.

  36. Hello, Can anyone help me with the following. I have data in 7 columns, 5000 plus rows. First 6 columns have numbers they refer to the value in column 7th which is text. Example

    10 0 0 0 0 0 H
    10 101 0 0 0 0 S
    10 101 10105 0 0 0 T
    10 101 10105 10 0 0 W
    10 101 10105 20 0 0 F

    I want to see the result in the new sheet like this.
    10 H
    101 S
    10105 T
    10 W
    20 F
    Column 5th and 6th value is "0" so the result should be "no value"

    Thank you for your help in advance.

  37. Hello, I would greatly appreciate some help. I would like to create a command which will do the following: when I insert a specific text on a cell this will equal to a specific number. For example , when I insert "TT" this will equal to 0.2. Or when I write HD this will equal to 0.1. This will then be summed up on another cell. I would like to see only the letters but not the numbers. Is that possible?
    Thanks in advance for any help offered.

    1. Hello Christos,

      If you can have just either of the values, you can use the following formula:
      =IF(A2="TT",0.2,0.1)

      Here A2 is the cell where you enter the necessary text.

  38. Hi. I need a formula where if C9 is YES then it gives a score of 5, If C9 is NO then its gives a score of ZERO. Help please, its sending me round the bend

  39. =IF(B2>0,C2=0,"sliding","rotary") i want help if it b2 greater than zero, and c2 equal zero, i need it sliding, but if not i need it rotary and this formula has something wrong

    1. Hello Ahmed,

      In order to meet both conditions, you need to add AND to your formula:
      =IF(AND(B2>0,C2=0),"sliding","rotary")

      I hope this helps.

  40. Hello,

    I have 1 list of 4000 emails and other list of 700 emails. How can I sort or check or highlight or check if that 700 is available or not in 4000 emails ?

    Many thanks.

    Best,
    Ravi

  41. Hello everybody can someone help me, i need to link two tables together with a condition .
    For example I have To tables tasks For week1 and tasks for week 2. And i want to make if the task didnt accomplished in week 1 it should be transformed automatically to week2.

  42. Time WOB: tons Top Drive RPM: Result of value of time
    when WOB>0 and Top drive
    RPM=0 like 9 minutes in

    a result column
    1:34:00 AM 0 44
    1:34:30 AM 0 44
    1:35:00 AM 0 43
    1:35:30 AM 0 44
    1:36:00 AM 0 43
    1:36:30 AM 0 44
    1:37:00 AM 0 42
    1:37:30 AM 0 43

    these exactly are recorded time based data which is recorded every 30 seconds, so i need to get the exact sum value of the first time column like 02:12:15 in a separate column when wob>0 and Top drive rpm=0 this is the same question above

  43. Hi everybody, could you help me please..i have two issues
    first one: if i have a time column like each cell have a time value like 12:51:00 and the other cell down in same column 12:52:00 and so on all the column... i need to make a function which let me get the sum of the time column as a value like from 12:51:00 to 12:59 and i need the result equal 9 minutes.
    second issue: if have i need to make a result if B2>0 and C2=0 i need D2= the value of A2.. thanks alot

    1. Hello Ahmed!
      1. Time in Excel is a fractional part of 24-hour period, i.e. 24 hours is regarded as 1. You need to deduct the first time from the last one to find the time interval. To display this interval in minutes, you need to multiply the result by 24 and 60 as there are 24 hours in a day and 60 minutes in an hour.
      =(F10-F1)*24*60
      Cell format is Number.
      2. You can use the following formula:
      =IF(AND(B2>0,C2=0),D2,"")

  44. suppose the quantity in "A1"cell =1000
    I want to type quantity in "B1"cell with following conditions -
    if quantity of "B1"is lesser than "A1",take the quantity as it is.
    if quantity of "B1"is more than "A1",take the quantity as "A1".
    if quantity of "B1"is same as "A1",take the quantity as it is.

    Pl send me the formula for above.
    Regards,
    Umesh M

    1. Hello Umesh,

      If you want to use the value in cell B1 as a condition in the formula, you need to enter the formula in a different cell:
      =IF(B1<A1,B1,IF(B1>A1,A1,B1))

      I hope this helps.

  45. Hi

    As I understand you would recommend using nested IF Function (for example : =IF(Con1,IF(Con2, "A","B"),"B") )instead of AND with if (=IF(AND(Con1,Con2),"A","B"))?

    Is that right?

    Many thanks.

    1. Hello Arash,

      Thank you for your question.

      The choice depends on your task. When you use nested IF, you are saying that the cell can comply with one of the conditions and return the respective result. For example, your first formula would look something like this: =IF(Con1,"A",IF(Con2, "B","C"))
      Excel would read it the following way: if condition 1 is true, then we should see "A", if condition 2 is true, then we should see "B", otherwise we should show "C".

      When you use AND with IF, you'd want both conditions to be met. So when you type =IF(AND(Con1,Con2),"A","B") you say that if Condition 1 and Condition 2 are true at the same time, you will see "A", if not then you will see "B".

      I hope this clarifies the way it works for you.

  46. I want to know if i add three cells value and result is 100. This 100 how its came in words in excel putting formula or others way to get?

  47. I need a formula that looks at multiple cells in a column and decides if the date shown in each cell falls between February 21, 2016 and February 27, 2016. If it does then it adds it to a running quantity in the cell where the formula exists.

    Thank you very much

    1. Hello Ty!
      You can use the following formula:
      =COUNTIFS(D1:D25,">"&DATE(2016,2,21),D1:D25,"<"&DATE(2016,2,27)) Where D1:D25 are the cells with dates.

  48. Hi, I would like to have equation for: If a2>=5; Efficient, If a2=3; Average, If a2=2; Poor, If a2<2 ;"" (blank).

    Thank you.

    1. Hi Mitesh,

      You can use several conditions in the formula:
      =IF(A2>=5,"Efficient",IF(A2=3,"Average",IF(A2=2, "Poor","")))

  49. Hi, I want formula of in lst column if date is 15.03.2015 in 2nd column March-2015 if date is 16.03.2015 in 2nd column April-2015. Please help formula.

    1. Hi, I want formula of in lst column if date is 15.03.2015 in 2nd column March-2015 if date is 16.03.2015 in 2nd column April-2015. Please help formula.

  50. I have a spreadsheet for stock market transaction. I need to do the following check. If it is a BUY(LONG) transaction, return profit as exit cost-entry cost. If it is a SELL(SHORT) transaction, function should return profit as entry cost-exit cost. My columns are D - BUY or SELL depending on type of transaction. G- Entry cost and K - Exit cost. I made the following formula .

    If(D2=BUY, K2-G2, G2-K2). It is returning a #Name? error. How to make this formula.

    Thanks for the help.

    1. Hello Aravind,

      Please make sure you include the text in quotes, e.g.
      =IF(D2="BUY",K2-G2, G2-K2)

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