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

  1. If TV personality is popular OR if they earn less than 100k per year thay get 10% bonus else the column should diaplay nil..how can i develop that function

  2. I need a formula that will look at E6, E7, E8, E9, and E11. If 2 of them are >9 add .5, if 3 of them are >9 add 1. Any suggestions?

  3. like
    =IF(E13="Salary"),(L13*$M$10),"Nil")

  4. Hi Svetlana Cheusheva,

    Kindly if you could guide, If E13 is equal to "salary" (text), it should multiply the values mentioned in Cell L13 and $M$10, otherwise N/A. Thanks

    1. Hi Aamir,

      Here you go:

      =IF(E13="salary", L13*$M$10, "N/A")

  5. I want to learn excel.

  6. Hi Dear,
    I need a formula to G16 where if F16 says "YES" then G16=C16-8

    1. Hi Fatema,

      Here's the formula for G16:
      =IF(F16="yes", C16-8, "")

  7. A1 = RECEIVED / CANCELLED / DECLINED (Dropdown)

    If A1="CANCELLED" then A2, A3, A4... will show CANCELLED
    If A1="DECLINED" then A2, A3, A4... will show DECLINED

    but

    If A1="RECEIVED" then A2, A3, A4... must be blank

    Please advise. Thank you

    1. Hi Brenda,

      Here's the formula exactly per your conditions:

      =IF($A$1="CANCELLED", "CANCELLED", IF($A$1="DECLINED", "DECLINED", IF($A$1="RECEIVED", "", "")))

      And I believe this simpler one will work as well:
      =IF($A$1="RECEIVED", "", $A$1)

  8. how to Segregate the Employees Categories from Excel sheet through Formula ? for example I have too many employees job title so I want segregate their profession project location wise through formula how can we do please help.

    1. Hi, Svetlana,

      how to Segregate the Employees Categories from Excel sheet through Formula ? for example I have too many employees job title so I want segregate their profession project location wise through formula how can we do please need your help to give me the Idea Soon.

  9. Using an 'if' function to put in todays date (using 'today()'), but want the date to remain static and not move on when the spread sheet is opened the next day

  10. I am trying to accomplish a multi-faceted IF statement.
    IF cell>02131<40 Then 3

    Is this possible? thanks

  11. I am interested to make cascading drop down for 6 columns.
    1st col having 5 variables/options,2nd dependent col having in all 44 variables(6 to 14 for each previous options),3rd dep col having 65 variables,4th col having 480 variables.5th &6th having corrosponding 480 values.is it possible? much data for Name manager. Please help.is there any another way?

  12. hi,i m having problem while calculating IF function with time.All cell are formatted for h:mm.
    A1 for start of duty say5:00,B1 for getting machine ready(allowed 2:00)say 8:00(more by1:00).now C1 shows working hours for machine(max value 4:00 hour).formula may be =IF((B1-A1)>2,4-((B1-A1)-2),4),HOLD GOOD for for GENERAL formate.with TIME format h:mm shows error/invalid.please help.

    1. HI SWETA,I TRIED FOLLOWING
      IF((time(hour(I17-G17),MINUTE(I17-G17),SECOND(I17-G17)))<=2:00:00,4:00:00,4:00:00-(time(hour(I17-G17),MINUTE(I17-G17),SECOND(I17-G17))-2:00:00)),BUT DIDNOT WORKED.

  13. Hi Svetlana,

    I hope you can assist me. I need to compare various DATES in different CELLS and get back the Appropriate Date. Below is the formula I put in, but I keep getting #VALUE!........what am I doing wrong???

    =IF(Y9<TODAY(),AB9,Y9),IF(AB9<TODAY(),AD9,AB9)

    I need the formula to look at each date then compare it to TODAY and provide the correct date back

    Thank you in advance!!

  14. I am comparing two cells, both of which have dates,to calculate the number of hours and minutes minus non working hours and weekends. That was the easy part.
    If a cell has a date, I want to leave the date as is, no change. if the cell is empty, I want to use the now function so it evaluates to the current date and time so my first function properly returns. First function:
    =(NETWORKDAYS(F2,G2)-1)*($O$2-$N$2)+IF(NETWORKDAYS(G2,G2),MEDIAN(MOD(G2,1),$O$2,$N$2),$O$2)-MEDIAN(NETWORKDAYS(F2,F2)*MOD(F2,1),$O$2,$N$2)

  15. Hello again,
    I found a solution to my problem between your answers
    =IF(OR(AND(B1>=8;C1>=8;D1>=8);AND(B1<=0;C1<=0;D1<=0));"8,0";"")

    Thank's!

  16. Hello Svetlana!,
    I want a formula if possible like this =IF(F2=.....;F2*1,5;" ")
    but in column f there are only names and numbers(in €) for example 10,00€ and I want the word in order to choose only the ammounts

    thank you for your help!

  17. Hi Svetlana,
    Colud you help me abut this task. I want to create a single formula for the following "problem":
    A1 is 8 if the cell's between range B1:D1 is blank
    but alsoo i want for the
    same cell A1 to be blank (no data) if the cell's between range B1:D1
    contains number 8.

    Thank you.
    Best regards.
    P.S. this e-mail is correct

  18. Hi Svetlana,
    Colud you help me abut this task. I want to create a single formula for the following "problem":
    A1 is 8 if the cell's between range B1:D1 is blank
    but alsoo i want for the
    same cell A1 to be blank (no data) if the cell's between range B1:D1
    contains number 8.

    Thank you.
    Best regards.

  19. Hie Svetlana Cheusheva
    Please help me on the following.
    In a cell accepted Entry can be: Certificate, Diploma, Advanced Diploma, Degree or Master with the following amounts respectively, 5800, 8900, 12000, 15000 or 18000.
    Help me to write the If function so that such values can be used to be assigned to the respective variables.

    Thank you.

  20. Can you help please, I need a formula for:
    If G21 is <= to J3 then * by K3 but
    If G21 is <= to F3 then * by G3

    J3 and F3 are fixed amounts
    K3 and G3 are currency figures
    Thanks

  21. Dear,
    I have
    0-19 =Cat-3,
    20-49=Cat-2,
    above50 =cat 1,
    how to use if formula
    pls help
    vaule cat
    19
    24
    25
    35
    12
    45

  22. Hi i need some help with a formula if possible. I'm trying to do the following.
    In Cell C1 i have an amount "£"
    In cell E1 i want the word "paid"
    In Cell F1 i have the following "=SUMIF(E1,"paid",C1)"
    In Cell G1 i have this formula "=IF(E1="paid",NOW(),"Outstanding")"

    Now the problem i'm having is that if i copy this down my spreadsheet everytime i change a cell to "paid" every cell that already has a date in it changes to the date i make the new entry.

    Is there a way to keep the dates.
    For example.
    I input "paid" into a cell yesterday and the "date" shows as 4/11/15
    i put a "Paid" into a cell today and the "date" shows as 5/11/15 but keeps the previous day as is.

  23. Hi,

    I need some assistance with this...

    For Example

    =IF(G6>0,"FR")AND(H6+I6+J6=0,"NR")

    1. Hi Iris,

      I believe the correct syntax is as follows:
      =IF(G6>0,"FR", IF(H6+I6+J6=0,"NR", ""))

  24. It is to good.

  25. I am trying to get an IF or SUMIF formula to work but having slight problems arise. What I am after?! If cell A is 6, I need cell B to = Cell D x Cell E. Very simple I am sure however I cannot seem to figure it out. Any assistance would be appreciated. A real life example of what I am trying to achieve… If Sam buys 6 or less oranges it will cost her $1 per orange making it $6 however if Sam purchase 7 or more she will get oranges for 95cents each making the total $6.65 if she purchased 7.

  26. I found that if the text strings in the cells that I want to test are "TRUE" and "FALSE" then my if formula is not working as desired.
    Using your example in the above article how would you handle if your column C heading is 'Delivered (TRUE/FALSE)' and column D is 'Action Required (Yes/No)' ??

    I am able to control it if the string is anything other than 'true' and 'false' Are these exceptions or am I missing anything?

  27. Can you please assist me with a logical formula?
    Need to create a formula that will replace the word "YES" if Cell F2 is less than 25% of cell H2 or replace the word "NO" if this condition is NOT met.

  28. Hai i try to create a formula as follow
    I need the answer in C1.
    if B1A1------Late
    all other condition (if the cell is blank or the cell contains text) leave as Blank

    I try this one.... =IF(B1A1,"LATE",""))
    Can any one help me

  29. I am trying to create a formula that will replace the min value between d20:d27 if it is less than e2 with e2. my failed attempt is below:

    =IF(MIN(d20:d27)<E2,REPLACE(C20:C27,C20:C27,3,E2))

  30. I've built an evaluation form. It looks like this:
    Agent greeted the customer: Yes No N/A Score
    Agent sounded professional:
    Agent ended the call properly:

    Now; I want to have check/option button under each attribute. So, If I check Yes; it should place 4.5 under the score. If I check No; it should place 0 under the score column and so on...

    Thanks,

  31. im trying to use the if function to connect two tabs together.
    this is my command
    "=IF(B5=Daily!A:A,Daily!B:B,0)"
    even though everything is correct its still saying it false.
    can anyone help?

  32. it return error

  33. can anybody help me with this function ?
    =IF(C28=B2,($C$3-C28)/30*1.25,IF(($C$3-C28)/30>36,15)),if((($C$3-c29)/30>60,17)))

  34. Can you help me? I want to do this, I have a maximum mark of 320 teachers evaluation and If the mark obtained is higher than it has to write 320, if not it should write the mark itself. My problem is that E6 is already the answer of a condition statement and it returns 320 but this number is not recognised as a number by the following condition. I try to format it but it doesn't allow it.

    IF(Ensino!H$50>E6;320;Ensino!H$50)

  35. I figured it out if anyone wants to know

    =IF(OR(D3="",TODAY()>=C3,AND(D3"",D3>=C3)),"Yes","No")

  36. I figured it out if anyone wants to know:
    =IF(OR(D3="",TODAY()>=C3,AND(D3"",D3>=C3)),"Yes","No")

  37. HI ID NEED A FORMULA THAT IF COL a1 HAS THE LETTER H IN COL a2 WILL ADD 1

  38. Hello,

    Sorry it's not my day today... I do a loop with my brain :D
    So if somebody can help me to solve the following issue, it would be great.

    I would like to plan maintenance for equipments so I gather all the SMU & Last Maintenance date for each of my equipments.

    I compare if I the last SMU value is > of x time the schedule in column E (every 100, 250, 500, 1000, 2000 hours and so on)
    If the value of the Last SMU is superior of the Schedule I should find the next schedule SMU

    Below my table:

    Column B Column C Column D Column E Column F
    Last Job date Last SMU UNIT Schedule Next scheduled JOB SMU
    9/28/2015 64881 HRS 100 65000
    9/28/2015 64881 HRS 250 65000
    9/28/2015 64881 HRS 4000 68000
    9/28/2015 64881 HRS 6000 66000

    And the formula... It's working... but I need to simplify it in order to check up to 100 times the schedule in column E.

    Thank you in advance.

    =IF(C9>E9,IF(C9>2*E9,IF(C9>3*E9,IF(C9>4*E9,IF(C9>5*E9,IF(C9>6*E9,IF(C9>7*E9,IF(C9>8*E9,IF(C9>9*E9,IF(C9>10*E9,IF(C9>11*E9,IF(C9>12*E9,IF(C9>13*E9,IF(C9>14*E9,IF(C9>15*E9,IF(C9>16*E9,IF(C9>17*E9,IF(C9>18*E9,IF(C9>19*E9,IF(C9>20*E9,IF(C9>21*E9,IF(C9>22*E9,IF(C9>23*E9,IF(C9>24*E9,IF(C9>25*E9,IF(C9>26*E9,IF(C9>27*E9,IF(C9>28*E9,IF(C9>29*E9,IF(C9>30*E9,IF(C9>31*E9,IF(C9>32*E9,IF(C9>33*E9,IF(C9>34*E9,IF(C9>35*E9,IF(C9>36*E9,IF(C9>37*E9,IF(C9>38*E9,IF(C9>39*E9,IF(C9>40*E9,IF(C9>41*E9,IF(C9>42*E9,IF(C9>43*E9,IF(C9>44*E9,IF(C9>45*E9,IF(C9>46*E9,IF(C9>47*E9,IF(C9>48*E9,IF(C9>49*E9,IF(C9>50*E9,IF(C9>51*E9,IF(C9>52*E9,IF(C9>53*E9,IF(C9>54*E9,IF(C9>55*E9,IF(C9>56*E9,IF(C9>57*E9,IF(C9>58*E9,IF(C9>59*E9,IF(C9>60*E9,IF(C9>61*E9,62*E9),61*E9),60*E9),59*E9),58*E9),57*E9),56*E9),55*E9),54*E9),53*E9),52*E9),51*E9),50*E9),49*E9),48*E9),47*E9),46*E9),45*E9),44*E9),43*E9),42*E9),41*E9),40*E9),39*E9),38*E9),37*E9),36*E9),35*E9),34*E9),33*E9),32*E9),31*E9),30*E9),29*E9),28*E9),27*E9),26*E9),25*E9),24*E9),23*E9),22*E9),21*E9),20*E9),19*E9),18*E9),17*E9),16*E9),15*E9),14*E9),13*E9),12*E9),11*E9),10*E9),9*E9),8*E9),7*E9),6*E9),5*E9),4*E9),3*E9),2*E9),1*E9)

  39. Hello Everyone,

    If I could please have your help.
    =IF(AND(D4="",TODAY()>=C4,OR(AND(D4"",D4>=C4))),"Yes","No")

    I need an if function that con do the following:
    If return date D4="", and today's date is greater or equal to C4 then Yes, otherwise No. If D4 is not equal to empty, then D4>=C4, Yes otherwise no.

    I can't seem to figure it out =(

    1. I am not sure to have fully understood your query but try this way..

      =IF(D4>0,IF(TODAY()>=C4,"YES","NO")

  40. Hello,

    I am trying to do something along the lines of if Aetna, BCBS and so on are typed in the J5 field then Multiply K5, L5 or M5, etc. by the % that is entered in AJ5. Is there a formula for this?

  41. having a hard time getting this to work.
    voice and screen true - yes, false = no

  42. I have door numbers listed in boxes across. I need a formula that totals the number of boxes filled in with any number. 201, 202, 203 = 3

    1. =@COUNT( DID IT.

  43. i want to know haw to get value to close value by if function

  44. Hi
    I am trying to create if statement that has both text and number here is the code:

    =IF(U18="YES",VLOOKUP(W18,'Assumptions and Factors (2)'!U148:U150,1,VLOOKUP(Q18,'Assumptions and Factors (2)'!W110:X135,2),VLOOKUP(Q18,'Assumptions and Factors (2)'!Z110:AA135,2)))

    it is to create manhours on specific pipe sizes (Q18=3.00,'ADDS IN A MODIFIER,but if W18 is not None,1B,ET1, it goes to another table modifier)

    I do get #value when I do a calculation step it shows me but I just cannot seem to get pass this.

    any direction would be helpful
    Thank you

  45. I am attempting to return a set of responses should a series of the following exist...

    If Material/Service/Other COlumn = "Service", MSA column = "N" , COI Column = "N" , & COI Valid/Expired = "Expired"......= "No COI and MSA"

    and then in the same cell

    if(and(Material/Service/Other Column="Service", MSA Column="N", COI Column="Y",& COI Valid/Expired="Expired"...="Expired COI and No MSA"

    and continuing with the same cell and adding on to the above...

    If(and(material/service/other column="Service", MSA Column="Y", COI Column="N",& COI Valid/Expired="Expired"....="No COI"

  46. Due Date Date of Deposite Delayed days Penalty Amt
    10/21/2015 10/26/2015 5 2000

    Interest for Late Filing of Return
    Particular Amount
    Up to 30 day 2,000.00
    Above 30 day 5,000.00

    how to put IF formula in penalty amt column if file the return before 30 days (penalty Rs.2000) after 30 days (penalty Rs.5000) and on the same day (no late file the return)

  47. Hi,

    I need a =if(or) formula

    =IF(OR(G153="Sta",451)=IF(G153="Fra",939)=IF(G153="LOG",294)

  48. Hi, I have to collect samples of employees dust exposure at a underground mine.
    Here are the conditions of re-sampling a sample dependent on the lab results:
    Underground
    • Below 0.3 mg/m3 (Re-sample one sample)
    • Above 5 mg/m3 (Re-sample one sample)
    • Above 10 mg/m3 (Re-sample two samples)

    Can you please help me with a formula?

  49. Hi I want a formula which is suitable for my data entry sheet Ex: if any party name or amount entered into sheet1 would change the amount in sheet2 simultaneously with the same name and amount.

  50. I am writing a spreadsheet where column d has a validation so only 6 options are available from a drop down menu. I would like to have the next cell auto populate with a specific value based on the previous choice what would my formula look like.

    ex: row 1column d reads refrigerator column e should populate 0001
    row 2 column d reads stove column e should populate 0002

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