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

  1. Can someone help? Please
    G2 = TODAY()
    F17=Date completed

    Formula Needed - Does the Year in the F17 Fall within (=) this year, if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

  2. Hi, I'm trying to figure out an Excel equation for the following conditions:
    If selected cell is less than 99, then the output is 0. If selected cell is greater than or equal to 99, then the output is 1. Does anyone know how to do this all in one equation?

    1. Hi!
      Assume that your cell is A1:

      =IF(A1>=99,1,0)

  3. =IF(C10>10,10,C12)

    What do you mean with "figure shown"?

  4. hi..

    need a formula..

    if the cell value more than 10, use 10...if less than, use the figure shown. (10 value is auto sum from the above cells)

    Please assist..thanks

    1. =IF(C10>10,10,C12)

      Assume that C10 contain your SUM, and C12 contain another desired value.

      What do you mean with "figure shown"?

  5. Hi,

    In excel One cell value contains 5 years,3 Months,20 days and another cell value contains 4 Years,3 Months,3 days, I wan total of these two cell value in third cell (9 Years,6 Months,23 Days) Please help me to find out the formula. Please.

  6. I want to do a formula for if columnn a >= column b then yes, if column a < column b then no, and if blank it won't count. Please help. I can't figure out the blank portion.

    1. =IF(OR(ISBLANK(A1),ISBLANK(B1))," ",IF(A1>=B1,"YES","NO"))

  7. Hi i wanted to know if i could use the IF forumla in the same ceel, e.g Cell e20 shows a percentage (54%) which has to be typed in. I need that to change so that if its below 20%, it shows 1. If between 20/40% it shows 0.75, if between 40/60% it shows 0.25 and if 60/80% it shows 0.

    Can this be done?

    1. NO it can't. :) Near that cell is ok.

      1. Thank you! Is it possible to do it in another cell but still have the reulsts showing if entered manually in that cell? (sorry if that doesnt make sense!)

        1. No. The problem is that you can't insert in the same cell a VALUE and a FORMULA.

          Perhaps you can use this:
          In cell F20

          =IF(E20<20%,1,IF(E20<40%,0.75,IF(E20<60%,0.25,IF(E20<80%,0))))

          1. Ahhh okay, I assumed you could lock cells. Thank you!

            I tried that forumla and it comes back with FALSE :( but thank you so much for your help!

            1. I don't know why.
              Please share or send me the file to correct that error.
              remindfwd[at]gmail[dot]com

  8. i have one query if you could help me i need to take the total amount for management and non management suppose in 1 column i keep the job band 1 to 12 employee wise this column including management and non management and another column i keep the salaries for all those employee so i need to segregate the salaries for management and non management by selecting job band for example band 1 to 5 salary should come under management and from 6 till 12 salary should come under non management. please help me to fix this formula.

    1. Please send me a sample. remindfwd[at]gmail[dot]com

  9. I need a formula that IF there is any data/text in the cell it will EQUAL another cell, BUT... I want the cell with the formula to display the data/text, NOT the result of the formula. I want to then auto sum the RESULTS at the bottom of the column. So if a person marks an "X" in the cell, I want it to = an adjacent cell, but I want to SEE the "X", not the number. Then I want to add the number results and get a total. Is this possible??

    1. You have 3 columns
      Name (or ...) Number (or value) Selection

      The formula I use is:

      =IF(C2="X",B2,0)+IF(C3="X",B3,0)+IF(C4="X",B4,0)+IF(C5="X",B5,0)+IF(C6="X",B6,0)

      In a single cell is get a total (SUM) of numbers who have selections "X".

      That is?

  10. i need one help , to fix the my JV sheet, i need and Debit ("D") if my one cell amount is more than +1 , if the cell value is 0, "D","C") formula. But its not working..

    Hope any one can help me to solve this issue,

    Thank You So Much
    Shameer

  11. True or False will work for above. Please help.

  12. Hello.
    G2 = TODAY()
    F17=Date completed

    Formula Needed - Does the Year in the F17 Fall within (=) this year, if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

  13. =IF(T16<0,"Fail","Pass",if(L15="",""))
    i need correct formula. if ceel is less then 0 then pass otherwise pass and cell is blank then blank

    1. =IF(T16<0,"Pass",IF(L15="","","Fail"))

      It is what you want?

  14. the interest= 5% of price if it is <100,000, 7% if price is between 100,000 and 200,000, 10% if else.
    how can i find the function of this?

  15. my question is.
    if a1, "dddd" = "sunday" , "restday", "workday"

    how can i do this in excel.

  16. I want to use one formula that will round up all figures except ones that end in ".00" That is, $12,000.00 would stay at $12,000 but $12,000.01 would round up to $12,001. Thanks for any help...

  17. Can you help me with the IF Formula for if the % score is below 60% then it should be "0" marks, and if it is between 60.5% to 80% then it should be half of the Marks defined like if it 20 Marks then automatically it should show "10" and if it is above 80.5% then the full marks that is "20" marks.

  18. How can I do this in one formula (or any alternative way)
    (expected output is in column C)
    Dash indicated columns (4 rows)
    Column A - Column B - Column C
    positive - positive - excellent
    positive - negative - good
    negative - positive - fair
    negative - negative - poor

  19. Hi
    I need the following:
    IF there is a date value in column D, then Matter Age (Column E) is D-E.

    IF, however, there is not a date value in D, then matter age(Column E) is B3(CELL)-Date Received (column C).

    I only want to count business days please, not weekends.

  20. DEAR ALL,
    HOW CAN I CONSTRCUT FORMULA FOR THE NEXT SET OF DATA
    1-20000 1.0
    20001-30000 0.95
    30001-60000 0.80
    60001-85000 0.60
    >85000 0.4
    EXAMPLE : FOR 45000
    =(10000*1.0+20000*0.85+(45100-30000))/45000

  21. I want to have a narrative for the following:
    0 = Not true
    1 = Occasionally
    2 = Often
    3 = Very often

    I'm trying to do a formula where, if I type in "1", it would pop up with "Occasionally" in the cell next to it. Basically, whatever number I type, I'd like the narrative to be next to it.

  22. If C10 is "P"then C11 should "N", If C10 is "A" then C11 should "Y", if C10 is "O" then C11 should be "O" how to use the formul for this.

  23. Hi, can anyone please help me with below scenario:

    IF A2 is Date(today's Date) then my B2 Should be "completed". and if my A2 is "-" then B2 should be "WIP".

    Please help

  24. Hello
    Can you help with the formula below.

    If J3, J4 J5 <=79.99 turn red

  25. Hello,

    Can you please help me with the following formula?

    Customer Loan Amount = J2
    Customer Interest Rate = K2
    Available Interest Rate = L2
    I would like to divide $125,000 by the customer loan amount to get value: X
    For example a customer with a 250000 loan:
    125000/250000 = .5%
    If the customer interest rate (K2) and available interest rate (L2) difference is greater than or equal to X, I would like it to return Yes and if the difference is not greater than .5 I would like it to return No
    The L2 amount would be changed daily, and applied to the entire column.
    The J2 amount has $ I do not know if that effects anything.
    Percentage % sometimes used sometimes not.
    If there is a blank in any of the applicable values I would like it to not apply the formula.
    Right now I have:
    =IF(K3-L3>=125000/J3,"YES","NO")
    Is this correct?
    Thank you for your help.

    Best Wishes,

    George

  26. So I am using the IF function.
    I want to do multiple IF's in 1 Cell.

    If A2 is No then Joe
    If B2 is No then Benn
    If C2 is No then Tom
    IF D2 is No then Lee
    If E2 is No then Mick
    If F2 is No then Anne
    If G2 is No then Lynne
    If H2 is No then Terri

    I need these in 1 formula so in J2 if all were No it would have that list of Names.

    Can anyone Help?!?!?!?!?

  27. Hi, i'm really confused trying to fixed this. hope you can help me with this.

    =IF(OR(L36="Pending", L36=""), 50, 200)

    question: based on the above formula,
    i want cell B to formula Yes if cell A contain "pending"
    i want cell B to formula EmptyBlank if cell A does not contain "pending"
    in cell A contain : text and numeric of different content.

  28. I am trying to figure out how to use IF for a range.

    I want to check if Cell A1 is within 52 to 60 to be true. I tried the suggested formula but it is not working. =IF(AND(A1>=52,A2<=60),"OKAY","NO")

    Am I missing something?

  29. I need a cell to show a numerical value for ex
    If b2 is =999 then f2 =1
    what formula do I use?

    1. what I meant was;
      If b2 is greater than or equal to 300 or less than or equal to 999 then f2 equals 1

  30. One Order No. has 4 different sku and 3rd coloumn signifies the quantity of units ordered.. Blank space in first column represents that all sku s belong to order no in the above row. This is what I have:
    No sku Qty
    200090505 DO-NANO-CABLE-WRAP-TEAL 2
    DO-PICO-CABLE-WRAP-TAN 2
    DO-SMALL-CABLE-WRAP-TEAL 2
    DO-TINY-CABLE-WRAP-BEIGE 2
    200090494 BRAINSTO-PPS-PLN-NTBKA5 1
    DO-SMALL-CABLE-WRAP-BEIGE 1

    This is How I want Basically Transpose it in 1 single row: All sku's in 1 single row adjacent to the order:
    Column A B C D E F G H I
    Order200090505 SKU QTY SKU QTY SKU QTY SKU QTY
    Order200090494 SKU QTY SKU QTY

  31. One Order No. has 4 different sku and 3rd coloumn signifies the quantity of units ordered.. Blank space in first column represents that all sku s belong to order no in the above row. This is what I have:
    No sku Qty
    200090505 DO-NANO-CABLE-WRAP-TEAL 2
    DO-PICO-CABLE-WRAP-TAN 2
    DO-SMALL-CABLE-WRAP-TEAL 2
    DO-TINY-CABLE-WRAP-BEIGE 2
    200090494 BRAINSTO-PPS-PLN-NTBKA5 1
    DO-SMALL-CABLE-WRAP-BEIGE 1

    This is How I want Basically Transpose it in 1 single row: All sku's in 1 single row adjacent to the order:
    Column A B C D E F G H I
    Order200090505 SKU QTY SKU QTY SKU QTY SKU QTY
    Order200090494 SKU QTY SKU QTY

  32. Hi.!

    i want your help, i'm using "if" function pls review my function.

    =(IF(OR(T23:T27=B14),"01-101-101",IF(OR(B14=U23:U27),"02-101-101",IF(OR(B14=V23:V27),"02-102-101",IF(OR(B14=W23:W27),"03-101-101",IF(OR(B14=X23:X27),"03-102-101"))))))

    Note: Example
    B14 CELL Contains the word You
    T23 Cell contains the word Will
    T24 Cell contains the word Smith
    T25 Cell contains the word You
    T26 Cell contains the word Kill

  33. Hello.
    I hope you can help me. I need a total that will add only if the answer if yes. For example. If I have 20 quotes a day but I only sold 10. I only need to add those 10. I need those then to be showing on the total not the other 10.

  34. i want to know how to do in excel if i am going to check if the shipments will be on Jan., feb. mar and so on.. based on the production date..

    for example: i have production dates in feb. 1, 10, 26. i want to fall it in category 2, and if march:3 april:4 and so on..

    can anyone could help me??

  35. I want to grade students based on marks scored in six units and if score is less than 45% in any unit the result is fail if greater than 45 in all units the result is pass.(To pass student must score above 45% in all the six units)

    Formula am using: =IF(C11>=45, "PASS", "FAIL")
    C11........J11
    HOW CAN I DO IT

  36. What is the function please for the following:

    if the sum in D21 is lower than 1000

    so

    lock the cell in I6

  37. I am trying to read text in columns D5 through D30, and are looking for items equal to CRN. From there I would like to get the value of J5. Every time it picks up CRN in column D I would like it to keep a running total.

  38. Hi,
    I am trying to set a formula for C8:
    C7 (amount of hours worked, amount will be entered manually in hh:mm format)
    C8 - if a person did work more than 36hrs 30mins, then C8 needs to show 36:30 but if less than 36:30 amount should be copied from column C7.

    Example:
    C7= 32:30
    C8= 32:30

    Or

    C7= 42:00
    C8= 36:30
    Please help,
    Jane

  39. pls help me for the formula IF when it is for 5 times
    If Ach is >=95% score 1
    >=97% score 2
    >=99.6% score 4
    >=101% score 5
    and >=103% score 6

  40. I have a doc where i have one tab "January 2017" where we enter date of call example "01/03/2017", enter time of call "7:00 am, select from list of call requesting service "Car or Shuttle" then complaint list +10 minutes. I want to continue using this sheet for the entire year, while on tab 2 "Call Summary" count all the calls which i was able to do using =COUNTIFS('January 2017'!I2:I26,"CAR",'January 2017'!J2:J26,"Wait Time +10 min") Now what i need is to have the formula first match Summary Cell A2 which i enter date of 01/03/2017 and match it to all columns of A2:A500 in tab "January 2017" and if they match (all calls matching 1/3/2017) then run formula =COUNTIFS('January 2017'!I2:I26,"CAR",'January 2017'!J2:J26,"Wait Time +10 min")ignore all other dates like those call on 1/5/2017

  41. 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..

  42. 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

  43. 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

  44. 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)

  45. 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

  46. 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

  47. 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!

  48. 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

  49. 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

  50. 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

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