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

  1. Hello
    i am trying to create a formula which should be reducing a principle amount monthly in a cell. For example if someone gets an advance of say $5,000 to be paid in 10 months, it should post a $500 every month in a particular cell. is that possible..

  2. IN THE A COLUMN HAVING VARIOUS DISPOSITION AND IN B COLUMN HAVING VARIOUS NO'S DIGITS IN CASE OF THE A COLUMN AND B COLUMN MATCHES WITH CONDITION NEED TO DISPLAY AS "ATTEMPT 1" AS LIKE NEED TO WRITE VARIOUS CONDITIONS NEED HELP.

    THANKS IN ADVANCE

  3. good day,
    I need a formula for

    If i fill D14 with value so directly the old value in D13 will have shift to D12 and d13 will take the value entered in D14 and the old value in D12 shift to D11 and old value in D11 shift to D10 and old value in D10 shift to D9
    Many thanks
    Majed

  4. Good day,

    I am trying to make a formula with text value and numbers using if in conditions and it is not work
    Example: =IF('January Performance'!EU8=0,$D8="Mining & Survey",Summary!$F$18)
    The formula above works, but when I add an option like below:
    Example: If('January Performance'!EU8=0,$D8="Mining & Survey",Summary!$F$18,'January Performance'!EU8)

    Adding formulas of the same king for multiple selection it's not working.
    Example: =IF('January Performance'!EU8=0,$D8="Mining & Survey",Summary!$F$18,'January Performance'!EU8,If('January Performance'!EU8=0,$D8="Wash Plant",Summary!$F$19,'January Performance'!EU8)

  5. Say I have a column with numbers between 0 and 100 I need the next column to display a letter based on that number. So if the number is below 25 I need it to read L between 25 and 50 to read M 50 to 75 H and 75 and above E. How would I go about doing that

  6. Hi,

    I am working on one excel where I need to check different scenarios using IF condition but unable to do so. Please can you help me as to how I can work on the same to get the accurate result.

    There are more than1 if condition but not able to get the result.

    Below is the excel which I am working on along with the condition that need to but fulfil.

    Assigned User » Account Calculated User » Account Last Logged On User » Account Asset » Asset Status Asset » Inventory Date Asset » Short Description Asset » Serial Number Asset » Inventory Agent Asset » Physical Inventory Date
    SATHISH_KUMAR_G_PANDALA spandala spandala Installed 20-12-2016 II01-FCPP882 FCPP882 SMS Need Result in this column
    sridhar_pasupuleti spasupuleti spasupuleti Installed II01-6TKB9F2 6TKB9F2 SMS 25-03-2016 Need Result in this column

    Condition Result
    Asset Inventory date is blank. Inventory Date is Blank
    inventory date non blank,but last logon user is blank Last Logged on User Blank
    Last logon user non blank but inventory date more than 90 days Inventory not within 90 days
    Asset Physical inventory data current month Recently updated by OSS
    Compare Calculated and last logon user. If Different Calculate and Last Logged on User are Different
    Compare Calculated and last logon user. If Same, then compare Assigned User and Calculated User. If same. All Users are Same

  7. I have a workbook with 2 tabs. I want to get the value of a cell in tab 1 and based on that value, take the contents of another cell in that same tab and put it into the cell of tab 2.
    Example: Tab 1 has verbiage in cell A2 and a value in A3
    If the value in A3 = "fail", then take the value in A2 and put it in Tab 2, cell A1.
    Hope that makes sense
    Thanks!

  8. I need a formula for

    If column A is 10 value should be alex
    if column b is 11 value should be syam
    if column c is 12 value should be das
    if column d is 13 value should be mathew etc

  9. Analyze the quality of these volume estimates by categorizing the quality of the annual volume estimate versus the actual annual volumes for each dealership into the following categories:

    • Display“Excellent”if the estimate is within 5%(higher or lower)of the actual sales volume. (Hint: For example, if you wanted to determine if the value 26 is within +/– 25% of 40, you would need to test this value to make sure that both 26>=40–.25*40 and 26<=40+.25*40.)

    • Display“Good”if the estimate is greater than 5% higher or lower,but within 10% higher or lower of the actual volume.

    • Display “Poor” if the estimate is greater than 10% higher or lower.

    I've tried everything I can think of and can only get a response of Excellent. Estimate sales are 1540 and Actual sales are 1617

  10. I have a data set where I'm using two criteria to make a calculation, but my if statement is giving "False" as an output. Also, what would be the process if I wanted to include in the formula to calculate the cost based on whether the cost type column is one type vs. the other?

    Ex:

    Cost Type
    AB = Cell e1 Cost = f1
    CD = Cell e2 Cost = f2

    Columns:
    A B C
    Cost Type Units Cost Formula
    AB 10 10.00 =if(A:A=$E$1,=(B:B/F1))
    CD 20 20.00
    AB 30 30.00

  11. S.NO NAME TAMIL ENGLISH MATHS SCIENCE SOCIEL TOTAL AVERAGE
    1 SURESH 94 69 82 49 36 330 66
    2 PRAVEEN 85 68 19 28 59 259 51.8
    3 JOHN 88 54 58 69 58 327 65.4
    4 DINA 65 55 29 58 54 261 52.2
    5 HARSHA 69 89 86 60 59 363 72.6

    I WANT TO BELOW 40 MARK TAKING STUDENT RESULT WAS FAIL ABOVE 40 MARK TAKING STUDENT RESULT WAS PASS, KINDLY LET ME KNOW THE IF COMMENT,

  12. =IF(C2>DATEVALUE("01/01/1900"),"EMPTY")*IF(D2>DATEVALUE("01/01/1900"),"BALANCE ")*IF(D2>DATEVALUE("01/01/1900"),"DISPATCHED ")

  13. Hi i am trying to convert Likert survey data in a spreadsheet reflected as Strongly agree, Agree, Disagree and Strongly disagree TO 4,3,2, or 1 respectively in my spreadsheet.
    I have tried all kinds of IF options but keep on getting a 0 as "answer". What is wrong with this one for example? Thanks
    =IF(J2=Strongly Disagree,”1”,IF(J2=Disagree,”2”,IF(J2=Agree,”3”,IF(J2= Strongly Agree,”4”))))

  14. 1,20,000/
    I need spell number i.e;inwords

  15. ​What I am trying to do is to project the next maintenance date based upon the last date the maintenance was performed. Some maintenance occurs every 2 years, every year, every 6 month, every quarter, every month or 3 every week.

    Example:

    F2 G2 H2
    Maintenance Date Maintenance Performed *Maintenance Due
    9/14/2015 2Y 9/14/2017
    9/15/2015 Y 9/15/2016
    9/16/2015 6M 3/16/2015
    9/17/2015 Q 12/17/2015
    9/18/2015 M 10/18/2015
    9/19/2015 W 9/26/2015

    *
    *Cell H2: the formula will automatically calculate the maintenance date using data in cell F2 ..... BUT one formula will capture all of these criteria and calculate the correct date based upon the data in cell G2.

    The date formula is: =DATE(YEAR(F2),(MONTH(F2),DAY(F2)
    To project a date the formula for 2 years: =DATE(YEAR(F2)+2,MONTH(F2),DAY(F2))
    To project a date the formula for 1 year: =DATE(YEAR(F2)+1,MONTH(F2),DAY(F2))
    To project a date the formula for 6 months : =DATE(YEAR(F2),MONTH(F2)+6,DAY(F2))
    To project a date the formula for 3 months: =DATE(YEAR(F2),MONTH(F2)+3,DAY(F2))
    To project a date the formula for 1 month: =DATE(YEAR(F2),MONTH(F2)+1,DAY(F2))
    To project a date the formula for 1 week: =DATE(YEAR(F2),MONTH(F2),DAY(F2)+7))

    Now what I want to is to create and if/then, or if/or formula or whatever is correct with all of these formulas so that it pulls the correct date based upon the data in G2.

    2Y = 2 Years
    Y = 1 Year
    6M = 6 Months
    Q = Quarter
    M = 1 Month
    W = 7 days

    EXAMPLE: I think the first part of the formula would look like this for IF/THEN or the IF/OR so that searches through the criteria and post the correct date..

    =IF(G2)=2Y

    I thought the whole formula would look something like this but I don't even know if its correct, just something I came up with in my head...

    =IF(G2)=2Y,"DATE(YEAR(F2)+2,MONTH(F2),DAY(F2))","DATE(YEAR(F2)+1,MONTH(F2),DAY(F2))","DATE(YEAR(F2),MONTH(F2)+6,DAY(F2))","DATE(YEAR(F2),MONTH(F2)+3,DAY(F2))","DATE(YEAR(F2),MONTH(F2)+1,DAY(F2))","DATE(YEAR(F2),MONTH(F2),DAY(F2)+7))")

    but the formula is not calculating and there are errors

  16. These are my constraints: I must use the "if function" to search for a "phone " in cell box...I know it is awkard. But if I have three cellphone Sony for $500, Samsung for $450,and Apple for $700... how would I set up an "if statement" that would return the price if enter the "word" in a certain cell?

  17. Hi there,

    Could someone offer assistance with:
    If user selects "No" in column B, then user can enter a number in column C. However...

    If the user selects "Yes" in column B, then user cannot enter a number (or anything) in column C.

    Thanks so much for the help!

  18. I am trying to use IF statement to get that my dates match, but it gives me false when they are the same dates. Is there another function I should be using for dates because Excel recognizes them as text? IE: 06/02/2004 to 6/02/2004. Is it because the second value is missing a zero in front of it?

  19. Hi Jumana,

    Try this:-

    Good Good =IF(A2="Good",A2,"Error")
    Bad Error =IF(A2="Good",A2,"Error")
    Not Good Error =IF(A2="Good",A2,"Error")

    enjoy...

  20. Hi,

    I want to check if the text in one cell follows directly after text in the cell above. I am not sure how to compare that.

    Ex: If Cell A has the words "Good", I want to see if the cell directly below it has the words "Bad". If it is anything other than "Bad" I want it to say ERROR or False.

  21. Hello sabrina,

    Please use below text formula to check diffrence between 2 dates insted of datedif:-

    =TEXT(C22-B22,"yy") (this will come in d22)

    then type in next coloumn =IF(D22<5,"80 Hrs","120 Hrs")

    Enjoy ...!!

    1. Hi Peshiyaboy,

      Regrettably, this simple solution won't work because the result of the TEXT function is always a text string, even if it looks like a number. Consequently, the IF formula won't work correctly.

  22. Hello, I am having some trouble with using the IF function to calculate the vacation entitlement of employees with a certain tenure.

    I would like to stipulate that an employee whose tenure is less than 5 years would get 80 hours of vacation, but if 5 or more years, would get 120 hours of vacation.

    On my spreadsheet, column D displays the tenure. NOTE: the tenure is not a straight number, but a calculation made with a =DATEDIF function.

    The below formula is what I've been using:

    =IF(D2<5, "80", "120")

    However, the answer always ends up being 120 even if the value in column D is less than 5 years. Any assistance in helping me correct this would be greatly appreciated, and thank you in advance!

    1. Hello Sabrina,

      Most likely, the value in column D is a text string that looks like a number, and it is the source of the problem. To be able to say with certainty, I need to know your formula in D2.

      BTW, the result of your IF formula will also be a text string because you enclosed the numbers in quotation marks, and therefore you wont't be able to use those "text-numbers" in other calculations. So, you'd better put it this way: =IF(D2<5, 80, 120)

  23. =1+11+11+11+11+11+11+1

    please give me sum formula using this sequence after enter getting result but i want answer in excel formula

  24. I have a meeting in 15 minutes and need some excel help fast.

    I have two different excel workbooks. they both contain similar information. What I want is to find out which Product Codes in column A of workbook 1 are also in column B of workbook 2

    Not sure if this is a IF or a VLookup situation

  25. Hello,

    I think this is a really great site! I’ve learned a lot from everyone’s post.

    I was wondering if I could get some help. I am trying to write a formula that looks at a specific cell for a specific text, then looks at another cell for the value and then plugs in a dollar amount based on the value. It’s a tiering system which is looking at the text and based off of that text is trying to determine whether or not a payment is warranted. If I communicated this correctly, I am trying to say the following:

    I am looking for the formula to look in cell “I4” for if it says “M”, “G” or “L”. If “G” is in “I4”, then look at “E4” and see what the value is. If the value of “E4” is 0-9 then “0”, if “E4” is 10-20 then “1” and if “E4” is 21 or greater then “3”. If “M” is in “I4”, then look at “E4” and see what the value is. If the value of “E4” is 0-119 then “0”, if “E4” is 200-250 then “1” and if “E4” is 251 or greater then “3”. If “L” is in “I4”, then look at “E4” and see what the value is. If the value of “E4” is 0-1000 then “0”, if “E4” is 1001-1200 then “1” and if “E4” is 1201 or greater then “3”.

    This is what we initialed started with, but now we have to expand on it and develop a three tier system rather than it look for one value and place a value in it:

    =IFERROR((IF(I4=”M”,COUNTIF(E4,”>=80″),IF(I4=”G”,COUNTIF(E4,”>=120″),IF(I4=”L”,COUNTIF(E4,”>=26″)))))*3,””).

    I now need it to look for three different values for each letter and place a new value based off of that value.

    I hope that makes sense.

    Any and all help would be greatly appreciated!

    If nothing else, happy holidays to everyone on this site!

  26. I have this formula so far:
    =IF(B4>=1000,1,"0")

    But I want that if B4 is greater or equal to 1000 then it gets 1 point for every 1000 or 0 if less than 1000.

    please help!!

    1. Hi Khush,

      Try this one: =IF(B4>=1000,INT(B4/1000),0)

  27. if the time exceeded 31minutes the result should be "Missed" , if it did not exceeded result should be "ok"

  28. =IF(I10>0,G10-I10,IF(U10>0,G10-U10,O10))

    CAN U HELP ME IN THIS FORMULA
    G10 IS A SUM OF 3 CELLS

  29. i put a quantity 50 in A1
    and A2 is total quantity A2 (A2 IS A SUM OF B1 AND A1)
    i want if i put any quantity >0 in C1 or D1 then less the quantity from A2 if i dont put any quantity >0 then show me A1 quantity in A2
    can any one help

  30. How I do a formula in excel (column C) to calculate the grade of the persons using the following data.

    A B C
    Name Points Grade
    X 57 F
    Y 88 B
    X 95 A
    W 73 C

    Grades
    A = 90 - 100%
    B = 80 - 89%
    C = 70 - 79%
    D = 60 - 69%
    F = 0 - 59%

    1. Hi luis,
      Plz use below one and replay if u have any doubts...

      =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

      1. HI... check below

        =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

        1. Sorry to say that .. this is not pasting my orginal formaula,...
          i have formula.. but unable to give... while pasting it missed to give some strings...

          =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

  31. i have two sheets in the first sheet i have values and emp id and in the second sheet i want that if in the particular row of first sheet if it contains any value grater than 0 then show on the second sheet and if equal to and less than 0 then no show.
    like
    first sheet have two rows one of which is blank and in the second row value is shown so in the second sheet i want only that second row value
    i think u understand what i want to say

  32. i want add A1 cell and B1 cell but result display in B1 cell

  33. i want and a1 cell and b1 cell but result display in b1 cell

  34. I am trying use an IF and OR function. The formula below works perfectly, but the one below that (where the cell A1 is replaced with the words) doesn't work and comes back with #VALUE.

    =IF(OR(L6="Research",A1),"","N/A")
    =IF(OR(L6="Research","Sales"),"","N/A")

    an anybody help??

    1. I have been able to use your answer for Keith on 2nd December, thank you.

      Answer:

      =IF(OR(L6="Research",L6="Sales"),"","N/A")

  35. Hi, I am trying to display the value in the next column if a value is found from a range. So, I have one workbook with three worksheets.

    The first worksheet has a list of ID's numbers and vendor names. Vendor names would repeat in this sheet and the customer ID could repeat if they use more than one vendor.

    The second sheet has a distinct list of the vendor names along with a tier rating (1,2,3,4).
    I am attempting to lookup the vendor name from sheet 1, find it in the vendor name range from sheet 2, and when found, populate the tier rating.

    To take it a step further, though I could do this function separately after I have the first done, I have a 3rd tier that includes a point value associated with the tier rating. I'll be looking to populate the point value for each row in sheet 1 based on their tier.

    Any help is appreciated. I am caught on how to display the tier value since I am not simple doing a yes or no of whether the text is found, but needing to display corresponding information when the text is found.

    1. I think I need to use a vlookup, but I am just not have any luck.

      1. A little funny with my repeated post, but I did solve my issue of generating the tier using an index/match formula.

  36. This is an awesome article. I'm having problems though getting it to work if the cell it is referencing is on another tab? For example, if B8 on Sheet 2 is a Y, then I want B9 on Sheet 3 to format with a Y or an N if the B8 on Sheet to is an N and blank if B8 is a blank.

  37. I would like to take the value produced in Cell D28 via this formula =ABS(SUM(D12-D24)), if the Value in D12 is less than D24 and have it show on a second sheet called "Score" in cells G12-N12-U12-AB12-AI12. I am at my wits end. I am constructing a score sheet and the value in D28 is the handicap between the team averages listed in d12 and d24.

  38. Hello, Miss... Can I use "IF" Formula for conditional formatting?

    If value in A2 is GREATER than 5% of value of A1, then I want the cell (A2) to be Blue color.
    If value in A2 is LESS than 5% of value of A1, then I want the cell (A2) to be Red color.

    I already know conditional formatting... But can you please send me the correct formula for the above. Thanks.....

  39. Hello,
    Would u please tell me how to do the following in Excel
    if the value in the cell greater than by 0.2 of the value of the other cell display as up regulated
    if it is less than by 0.2 of the value of the same other cell display as down regulated
    other wise diplay as normal.
    thanks alot

  40. Please help,

    what's the formula for

    IF CELL A = GP AND CELL B>3000 ,"H" IF 2000 ,"H" IF 2000 ,"H" IF <150,"L", "M"

    THANK A LOT

  41. Is there a way to code a function in this example?
    IF a cell contains 1 word (of 3 choices) in A1, then I want the value in B1 to appear in C1.

    1. Hello Keith,

      You can enter the following formula in C1:

      =IF(OR(A1="word1", A1="word2", A1="word3"), B1, "")

      1. How can i do this for multiple cells and add them all together?

  42. Thank you. It is helpful. I was thinking about some free version of variation as well.

    Thanks!

  43. Quick Question:
    I wanted to match last names from two excel sheets, then select the values (DOB) to be ported to the other excel sheet. What would be the formula?

  44. =IF(N18<=5,"32A",IF(N18<=5,IF(H18="1x4Cx6","")))

  45. Hi can anybody Correct this Condition

    =IF(A3180,(A3+180),IF(A3>540,(A3-540),"")))

    1. Hello Pawan,

      Probably you meant this:
      =IF(A3>540, A3-540, IF(A3>180, A3+180, ""))

  46. I want create a formula with if condition i am depreciating the value by 33.34% for 1 st year & 2nd Year & 3rd year according to my % of depreciation from the 1st july'16 to 30th june'16 but its not working for current 2016, kindly check the formula & help me.
    AA Column type Z column has date Y Colume
    =IF(AA429="R",0,ROUNDDOWN(IF($Z$1-Z429<=365,Y429*100%,IF($Z$1-Y429<=730,Y429*66.67%,IF($Z$1-Y429<=1095,Y429*33.34%,1))),))

  47. Hi guys good evening!

    How to apply in excel the following formula:

    0 - 136 = tax exempted
    136 - 2500 = 14% tax
    >2500 = 350 plus 25% tax

  48. I can't figure out the "If" Formula.
    Cell B26 is blank, 0.00, or greater than 0.00
    Cell D26 = 970.53
    Cell E26 Formula, to be T1 or T9
    Cell F26 Forumla, to be an amount
    Cell G26 Forumla, to be an amount
    Need the following formulas....
    1) For E26... If B26 = nothing, or 0.00, then E26 = "T9" otherwise "T1". For E26 to be "T1", B26 needs to be greater than 0.00.
    2) For F26... If B26 = nothing, or 0.00, than F26 = D26 otherwise F26 is to be D26-B26.
    3) For G26... If B26 = nothing, or 0.00, then G26 needs to show a zero (not a blank), otherwise G26 is to equal B26.
    Thanks for your help.

  49. I am making a weekly schedule for 2 months. I have entered the days of the week in a column. I need that if the day of week be Monday,Wednesday, Friday then output A , and if it be Tuesday, Thursday, Saturday , then B. Please help with the command line.

  50. Cell A1 is calculated and gives a numeric value.(a time value)
    I have used IF function so that A1 can only show a maximum of 9:30 even if the calculated value is more than 9:30.

    A1 is then used by other cells to calculate other values.

    I want to be able to show the value in A1 like so :

    If A1 is equal to or greater than 9:30 then show cell value as 9:30

    If A1 is less than 9:30 then show that actual value

    Can anyone help ?

    Thanks

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