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

  1. hi Svetlana Cheusheva,
    can you please guide me to make a farmula for this calculation
    IF A16 and 9 and <12 than * 450
    hope you understand what i want in result like i need to multipuly the B1 with 150 if A1 is less than 6 and if it is more than 6 and less than 9 than with 300 and if A1 is more than 9 and less than 12 than multiply B1 with 450 and if A1 is more than 12 than multiply B1 with 600
    please rply ASAP

    1. Hi Sharfi,

      Here you go:

      =IF(A1<6,B1*150,IF(A1<9,B1*300, IF(A1<12, B1*450, B1*600)))

  2. Hi,
    Cani use If function for different criteria,actually i want to do ageing of invoices i want to know since how many days the invoice is pending outstanding.There are different no of days for each invoice i want to know the days bracket. i want the formulae as per below for eg.If cell A1306090,"90-days"

  3. Hi,
    I wanted to know how to do the below scenario in Excel using if function eg
    I have mentioned no. of days worked in a month cell(K1) and no. of working days in month another cell (J1),if K1<j1 then there is value mentioned in I1 which is got using this formula =ROUND(L3*12/100,0) or else 1560

  4. Hi there.
    I would like to highlight a row based on a certain cell being a value greater than 0. ie. if cell H8>0 "Highlight row 8 (or a range of cells)

  5. Hello Svetlana,

    I am not sure if I am doing this correctly. I would like to check in a row in the columns A through D for the keyword "PLAN". If it is present the result would be "Yes" and if not present for the cell to remain blank. I tried this function but it does not work: =IF(ISNUMBER(SEARCH("PLAN",A2:d2)),"Yes","")

    Thank you,
    Matt

    1. Hi Matt,

      Try this one:

      =IF(COUNTIF(A2:D2, "plant")>0, "yes", "")

      1. Hi Svetlana,

        Worked perfectly.

        Thank you,
        Matt

  6. I found another way to find my average % completed:
    =IF(O5>0,(SUMIF(F9:F15,"Active",I9:I15)/O5),"100%") where O5 equals total active students. This one stumped me when there were no active students. That is O5 is zero. Anyway, it works!

  7. I want to use IF to discriminate between individual turtles! I have drop downmenus describing each shell segment, e.g. box A segment one has a mark/ is white, box B the mark is single/double, Box C it is shaped like a circle, square, line, cone, other..then this combination of shell segment descriptions means the turtle is number 32. (All turtles have individual markings)
    How can I use if to return a specific individual identifier, rather than a yes/no, true/false answer?
    thanks for your comments in advance

  8. Hi Svetlana
    =IF(A1="Active",AVERAGE(B1),0)
    The above formula works for one cell, of course, but I need to construct it to work for a range of cells.
    If "Active" appears in column A,(A1:A9), then I need to calculate the average of all numbers in corresponding cells in column B, (B1:B9). Do I need to nest a VLOOKUP into this IF statement? Either way, can you please help?
    Many thanks :)

  9. Hello, I would like to increase each numerical value by $100 in every cell in a very large table that has a number, but if there is text in the cell, then I would like to simply copy the text from the call. Can this be done with the IF function?

    1. Hi Marlene,

      You can use a formula similar to this:
      =IF(ISNUMBER(A1)=TRUE, A1+100, A1)

  10. HI! I HAVE TEXT INPUT FOR A2 TILL A100, EACH TEXT INPUT IS EQUIVALENT TO A NUMBER, EX, IN A3 (LC) AND IN B3 I PUT THE EQUIVENT IS 0.75 , IF I PUT A FREE TEXT IN C1 (LC) I NEED TO DISPLAY THE EQUIVALENT IN D1.
    A B C D
    1/ BS- 1 LC 0.75
    2/ NC- 1.75
    3/ LC- 0.75
    4/ WL- 0.50

    EX ABOVE : I INPUT LC IN C1 , I NEED TO DISPLAY THE EQUIVALENT WHICH IS 0.75 IN D1 ( WHICH IS FROM B4) I WANT IT TO DISPLAY AUTOMATIC ONCE I PUT ANYTHING FROM COLUMN B IN C

    NEED YOUR HELP THANK YOU

    1. Hi MARRIAM,

      Try using the following VLOOKUP formula, where $A$1:$B$100 is your range of data:

      =IFERROR(VLOOKUP($C1, $A$1:$B$100, 2, FALSE), "")

  11. I WANT CALCULATE LIKE THIS:
    IF A1 Grater than date 31-05-2015
    than A2*12.36% otherwise A2*14%
    thats it but formula not giving me correct value
    so Plz help me on this
    IN ONE CELL
    =ROUND(IF(A1>"31-05-2015",A2*14%,A2*12.36%),0)

    AND I ALSO TRIED IN THE PLACE "31-05-2015" TO DATEVALUE("31-05-2015")
    BUT NOT WORKING.
    IT SHOWS #VALUE

    1. YES MY FRIEND I FOUND MY SOLUTION AND IT WORKS
      LIKE THIS
      =ROUND(IF(T8>=DATEVALUE("01-06-2015"),E8*14%,E8*12.36%),0)

      THANKS

      AND DOING GOOD JOB (GENIUS WORK)

  12. Good afternoon, I am trying to create a simple spreadsheet calculating a price per square foot value based on an optional selection of option A (wood material)or option B (carpet, which would be translated to square yards). both options A & B will also need to be increased by 20%. If I have all the correct numbers in the spread sheet, why cant I apply an if then statement to the column / cell I want to create the equation for? I am trying the following.
    =IF(H7=Option A,"((F7*D27)*1.20))",(H7=Option B,"(((F7*.111111)*D28)*1.20))"
    F7 represents the square feet
    D27 represents the Price
    H7 would be the field I would like to type option A or option B into
    thank you for your help.

    1. Hi Doug,

      I believe the correct syntax is as follows:

      =IF(H7="Option A", F7*D27*1.2, IF(H7="Option B", F7*0.111111*D28*1.2, ""))

  13. Hi,

    i want a formula that will not show data on another workbook if the column is blank eg

    yes no
    \
    \
    \
    \
    \
    \

    i only want it to show the yes column on another tab but dont want any space between the date. Is this even possible using this function? I know its possible from using filter but dont want to do this

  14. How to put a single formula for the following;
    If F31 is '0' then the value should be of H12...
    and if F31 is more than '0' then the value should be the value of F32.

    1. Hi Anwaar,

      You can use a nested IF formula like this:
      =IF(F31=0, H12, IF(F31>0, F32, ""))

      1. Thanks a lot!☺

        1. Good morning!
          Now could u also help me out for the following:
          I have different ranges of a quantity and one single cost for that particular range.
          For Ex. 5000 to 10000 = Rs. 120000
          11000 to 20000 = Rs. 115000
          21000 to 30000 = Rs. 100000
          31000 to 40000 = Rs. 98000
          and same pattern continues for further more values...

          request for early reply...
          thanks in advance

          Anwaar

  15. Hi Svetlana,

    I'm having difficulty with making a formula.

    What I want, is that if A1 is not Blank, that C1 Prints "N/A", but if C1 is not Blank, then B1 and A1 Print "N/A".

    I currently have: =IF(NOT(C1=""),AND(A1="N/A",B1="N/A"),IF(NOT(A1=""),C1="N/A","Error"))

    (If not possible please let me know as I've been having trouble with these recently, thanks)

  16. Hi,

    I need to compare the data between two xls sheets(cell to cell validation).

  17. Hi,

    I am trying to create an IF formula on a date cell that states if the date is before or equal to 01/02/2015 then give me 'A' otherwise give my 'B' (ie. if the date is passed).

    I have used =IF(D7<=01/02/2015,"A","B") but it does not work and gives me 'A' for dates before and after 01/02/2015.

    Hope you can help.

    Many Thanks

    1. Hi Ross,

      Just use the DATEVALUE function in the logical test:

      =IF(D7<=DATEVALUE("1/2/2015"),"A","B")

  18. Hi,

    I need a solution for below,

    Given data
    Name Subject marks
    Raj FFV01 70
    Vino FSO03 80

    In a desired cells, I need a formula that allows me to pick the Raj's "Subject" only and mark in the next cell. whereas if it's vino, Cell should be blank.

    Please revert if quest not clear.

    Thanks for your help in advance.

    1. Hi Raj,

      You can use a VLOOKUP formula similar to the below one:

      Subject: =VLOOKUP("raj", A1:C100, 2, FALSE)
      Mark: =VLOOKUP("raj", A1:C100, 3, FALSE)

      Where Name is column A, Subject is column B and marks are in column C.

  19. I am trying to change a formula from =IF(H2="2-bdrm/2-bth",$K$3,IF(H2="2-bdrm/1-bth",$K$4,IF(H2="1-bdrm/1-bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate?

  20. sorry it's not working

    1. Hi NIRAV,

      I am not quite sure what +c1 means. Anyway, if B1 is a date rather than a text value, the DATEVALUE function is not needed:

      =IF(NOW()-B1<30,C1,0)

      If B1 is a text value, then the following formula works fine:

      =IF(NOW()-DATEVALUE(B1)<30,C1,0)

  21. Hello,
    i want to make one condition like
    party payment days are going between 70 days so that's mention in excle
    inv. date amount 30-60 days ,60-90 days, above 90 days
    1 1/4/15 50000 0 50000 0
    pls give me that type of formula ,
    i use one formula =if(now()-datevalue(b1)<30,+c1,0) but it's now working
    pls give me other formula

  22. I am having a problem with the if function with this question9. cell C10 is equal to 0.
    a. If this condition is true, the current cell should be made empty (that is, equal to "").
    b. If this condition is false, the current cell should display the result of multiplying cell C10 (hours) by cell D7 (hourly wages). Use a relative reference to cell C10 and an absolute references to cell D7 so that the formula can be copied to other cells

    1. Hi Janay,

      Here you go:

      =IF(C10=0, "", C10*$D$7)

  23. HI!
    I'm trying to use a certain cell name from a list in my IF formula, but it will not allow me to use the character within the name without trying to use the characters function. is there a way to override this? Thank you!

    ex. =IF(D5=3Y) it's wanting to use that Y as a YEAR function and not allowing me to use it simply as a Y.

    1. Hi Stephen,

      If 3Y is a usual text string, then enclose it in double quotes like this:

      =IF(D5="3Y", value_if_true, value_if_false)

  24. hi, how to do this, ex:
    22.7 auto increment to 23 or 22.3 to 22 ?
    if 22.5 till 22.9 to be 23 or 22.1 to 22.4 to 22 ?

    1. Hi,

      Use this formula, your data in a column

      =ROUND(A1,0)

  25. Hello, Excel will not accept the following formula:
    =IF(F3= "yes", [G3*1.1], [G3])

    All I want it to do is return Cell G3 x 1.1 if cell F3 = Yes, else just cell G3. What is wrong with my format?

    1. Hi Paul,

      Just remove square brackets:

      =IF(F3= "yes", G3*1.1, G3)

  26. Hello

    I am trying to work out a formula for a table of rental charges for rooms in a house.

    Column E is 'date from' and Column F is 'date to' and column H is number of days. Rent runs from the 19th of each month.

    I used the following formula to work out rent cost for the number of days they were there which is basically saying

    'if the 'to date' is 19/05/15 then return the monthly rental value as shown in cell C14, but if it is not 19/05/15 then use the number of days multiplied by the daily rental value to give me an amount'

    =+IF(F4=DATEVALUE("19/05/2015"),C$14,0)+IF(F4DATEVALUE("19/05/2015"),(H4*E$14),0)

    I realised though that this only works if the month starts on 19/04/15. I have one tenant that moved in on 08/05/15 and i therefore need to adjust the formula to say that if column F= 19/05/15 and column E=19/04/15 then return the monthly rent but if F doesnt equal 19/05/15 and e doesnt equal 19/04/15 then use the number of days multiplied by the daily rent.

    Is this possible as when i adjust the formula with an AND it comes back as invalid.

    Any help would be very much appreciated!

    Thanks in advance

    1. Hi Amanda,

      Just add the following AND statement in the logical test of both IF functions:

      =IF(AND(F4=DATEVALUE("19/05/2015"), E4=DATEVALUE("19/04/2015")), C$14, 0) +
      IF(AND(F4=DATEVALUE("19/05/2015"), E4=DATEVALUE("19/04/2015")), H4*E$14, 0)

      1. Many thanks Svetlana!

  27. Hi I am new to Excel and i am trying to format a mailing list that has been sent to me. basically the query is
    if f2 is blank then copy the contents from E2 into F2. blanking out E2 when done. i hope that is clear any help would be appreciated.

    Thanks

  28. assit me with nested if statement of a traffic robot set up with out come or result of GO,Stop and prepare to stop.

  29. hi, i am currently trying to generate a dashboard for my company and am having trouble inserting data into particular categories. there is a general data input sheet and then the dashboard. on the dashboard there are 3 separate categories with about a dozen free rows beneath for data to be inserted. i wish to insert data from the general data input section based on value of dates given etc. eg if the date is now then insert under category 1 if more than 2 years away, category 2 etc. could you please suggest any ways that i can do this the easy way etc, I'm trying to avoid inserting the data manually under each separate category as there will be a lot to enter. thanks in advance (:

  30. I figured it out in case anyone ever has the same question :-)

    =If(ISERROR(MATCH(B1,A1:A1,0)),"False","True")

    Thanks!

  31. There are also hundreds of different names in my data so Column B will not always be the same length. :)

  32. Column B
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj

  33. If Column A contains Column B then Yes if not then No

    Can the formula take into account the case sensitivity as well?

    Column A Column B

    \\CCBF0FP21\DCLabels Doejj
    \\CCBF0FP21\DCLabels$ Doejj
    \\CCBF0FP21\DCReports$ Doejj
    \\CCBF0FP21\Direct$ Doejj
    \\CCBF0FP21\Review$ Doejj
    \\CCBF0FP21\DEV$ Doejj
    \\CCBF0FP21\Shipto Doejj
    \\CCBF0FP21\shipto$ Doejj
    \\CCBF0UD2A\UD1$\DOEJJ Doejj

    1. Sorry - the sample data on here is not displaying as 2 columns. To clarify:
      Column A
      \\CCBF0FP21\DCLabels
      \\CCBF0FP21\DCLabels$
      \\CCBF0FP21\DCReports$
      \\CCBF0FP21\Direct$
      \\CCBF0FP21\Review$
      \\CCBF0FP21\DEV$
      \\CCBF0FP21\Shipto
      \\CCBF0FP21\shipto$
      \\CCBF0UD2A\UD1$\DOEJJ

    2. Please Help :)

  34. Hi Tiffani,

    Supposing your dates are in column A, you can put the following formula in the "per diem" box:

    =IF(A1<>"", 20, "")

    The formula will return 20 if cell A1 in not empty. To format the returned number as currency, select the cell with the formula, press Ctrl+1 to open the Format Cell dialog, select "Currency" on the Number tab and choose the currency format you want.

  35. Please help me!

    For my job, I work a lot in excel. I have a spreadsheet to calculate per diem for drivers to receive on their paychecks. I have seven blank "date" cells. I'm wanting an if/then function so that if I write a date in one of the blank date boxes, it will automatically put $20.00 in the corresponding "per diem" box. Likewise, if the date box remains blank, the per diem box remains blank.

    Please help.

  36. Hi,

    I made a table for a high, medium,low with corresponding digits.
    Like for example,if the probability of failure is 1(rare) and the impact rating is low, then the result should be low.
    Now, my problem is how do i connect the table to my another sheet.

    please help

    1. try vlookup

  37. i have to do this in excel ,guyz help
    if b=250 value is 15
    if b=260 value is 12

    1. Hi Ajay,

      Here you go:

      =IF(B1=250, 15, IF(B1=260, 12, ""))

  38. I have my fantasy baseball spreadsheets. There are in two different files. File 1 has all of my palyers noted in column A and their positions in column B.

    I am looking to transfer the Info from Lets say "biils Row C" column A and B in my master file to "bills Row C" A and B in the updated spread sheet. I have the following formula that works on one line. but how do I get it to search the entire master sheet and pull that data over. The first C3 file is my updated sheet

    =IF(C3='[RE-ENTRY AS OF 5-5-15.xlsx]Hitting'!C3:C500,'[RE-ENTRY AS OF 5-5-15.xlsx]Hitting'!B3)

  39. I am trying to change a formula from =IF(H2="2-bdrm/2-bth",$K$3,IF(H2="2-bdrm/1-bth",$K$4,IF(H2="1-bdrm/1-bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate

  40. Please advise how to use below formula in excel

    =if(L2>60,"Active"),if(L2<60,"Critical"),if(L2<0,"Overdue")

    1. Hi Vinath,

      You'd rather put it like this:

      =IF(L2>60,"Active", IF(L2<0,"Overdue", IF(L2<60,"Critical", "")))

      Just pay attention, please, that the formula returns an empty string if none of the conditions is met, e.g. if L2=60.

  41. I need help with a formula using dates:
    What I am trying to accomplish is if cell G2< 7/1/14 then insert 7/1/14 but if not insert the date that is in G2.
    =IF(G2<DATEVALUE("7/1/2014"),"7/1/2014",G2)

    Thanks for any help,

    1. Hi Monica,

      Your formula is correct. If it returns a serial number rather than a date, you should simply apply the Date format to the cell (press Ctrl+1 to open the Format Cell dialog and select the Date format you want).

  42. Can you help me with this formula:

    =IF(AND(AE303="",AK303=""),Incident),IF(AND(AE303""),AK303="",(NOW()-(AE303))),(DATEDIF(AE303,AK303,"D")))

    I am trying to have the formula:
    (1) if cell AE303 and AK303 are both blank put in the word "Incident", but
    (2) if cell AE303 has a date in the cell and AK303 is blank insert number of days between AE303 and Today, but
    (3)if both cells have dates, subtract them and tell me the # of days between

    1. Hi Lana,

      Try this formula:

      =IF(AND(AE303="",AK303=""), "Incident", IF(AND(AE303<>"",AK303=""), TODAY()-AE303, IF(AND(AE303<>"", AK303<>""), DATEDIF(AE303,AK303,"D"))))

      Just keep in mind please that DATEDIF requires the start date (AE303) to be always less than the end date (AK303), otherwise a formula will return the NUM! error. An easy workaround is simply subtracting one date from the other in the last value_if_false argument:
      AK303-AE303

      1. I'm attempting a similar formula as step (2) noted in Lana's question but I cannot get it to work.

        The sheet has set due dates for a task to be done. There is a column for the completed date (C) and the days overdue (D) based on the due date.
        I'm trying to get the number of days overdue to show in column D (due date is in C2) if there is not a completed date in C, otherwise if there is a date in C, I'd like the cell to stay blank.

        Tried this formula, but it shows as FALSE when there is a date in C instead of coming up blank:
        =IF(AND(C5=""), TODAY()-$C$2)

        [Row 5 is the first row of data excluding the due date in row 2, so I'm intending on dragging down the remaining rows]

        Hopefully you can help me with this!

        1. Hi!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =IF(C5="", TODAY()-$C$2,"")

  43. i need a formula to display a number (2) if the cell says "yes" and display a (4) if the cell says "no"....
    can anyone help?

    1. Here you go:

      =IF(A1="yes", 2, IF(A1="no", 4, ""))

  44. i cannot figure the formula out help!!!
    In cell F20, enter an IF function that tests whether the order quantity in cell E20 is greater than zero. If it is, return the the charge for this item, which is the value of cell E20 mulitplied by cell D20. Otherwise, return a space by entering " " (that is, double quote, space, double quote). Autofill this formula into the range F21:F25.

    1. Hi Ana,

      Here's the formula for F20:
      =IF($E20>0, $E20*$D20, " ")

      Simply copy it down to other cells in column F:
      - select cell F20;
      - move the mouse cursor at the lower right-hand corner of the cell and you will see it changing to a plus sign (fill handle);
      - click the plus and drag it down to fill other cells with your formula.

  45. I have a tricky question...I want sheet 2 to return an if statement that calculates on sheet 1.: =if(MaterialBreakdown[SARA Title 313}="*YES", MaterialBreakdown[Material Name], NA. That is the formula I tried to use, but it gave me an error. I want all materials in column A to list in another sheet if they have yes in column C. I am really bad at explaining this!

    all help appreciated!

  46. I NEED YOUR HELP IN THIS FORMULA:

    =IF(K26="CHEMICAL TANKER",IF(AND(K26>=0,K26=5000,K26=10000,K2620000,550))))),IF(K26="GENERAL CARGO",IF(AND(K26>=0,K26=5000,K2610000,490))))

    THE ANSWER IS= #VALUE

    IS THERE ANY POSSIBILITY TO COMBINE MORE THAN ONE IF STATEMENT IN ONE CELL?
    I WANT TO ADD OTHER IF CONDITIONS IE,"BULK","CARGO" AND SO ON.

    IS THERE ANY LIMITATION FOR THIS CONDITION? TQ

  47. What if I want to say if cells F1, G1, and H1 are blank, I1 should be blank. If F1, G1 and H1 have numbers, then I1 should be F1+G1+H1.

    1. Hi James,

      Should I1 be blank if any of cells F1, G1, and H1 is blank or if all 3 cells are blank?
      And what if any of those cells contains a text value?

  48. Hi,
    I have two membership type groups; member and non-member (column A). I have registration start dates (column B) and registration end dates (column C). I need to confirm that the start and end dates for registration fall within the acceptable date range for both members and non-members. Members range (6/1/15 – 8/31/15); Non-Members (6/8/15 – 8/31/15).

    Thanks!

  49. Hi Guys, how are you?

    I imported data from TXT file when the field BDATE came like below:

    09/09/2013
    09/09/2013
    09/09/2013
    09/09/2013
    30/12/2011
    14/03/2013
    24/09/2013
    24/09/2013
    30/12/2011

    This format is British style (DD/MM/YYYY), when I convert to American style (MM-DD-YYYY) the date that begin > 12 were not converted.
    Somebody knows what to do?

    Thanks!

  50. Hi

    Need to insert if formula where there are four dates (one in each column) and need a yes answer if any of the dates are greater than one date but less than another date. eg. if any of the four dates were greater than 01/06/15 but less than 30/06/15 I need the answer to be yes

    Thanks. LP

    1. Lindsay,

      Please use this formula:
      =IF(OR(AND(A1DATEVALUE("01/06/15")), AND(A2DATEVALUE("01/06/15")), AND(A3DATEVALUE("01/06/15")), AND(A4DATEVALUE("01/06/15"))), "Yes","")

      Where A1, A2, A3, A4 are cells with the dates.

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