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 46. Total comments: 4818

  1. Can we apply multiple logical test in IF function?

  2. Hi
    Thank you for your valuable time and information.

    I would like to develop a formula in which I get the follow-up date automatically. For example, if I write "Voicemail" then Next column automatically capture the date after 7 days.

    So if I select "Left" the column automatically capture the date of next action
    VM 7th day from Today
    Not Answered 2nd Day from today
    Call Back Manually
    Reschedule Manually
    Email 3rd Day

  3. I'm looking for a way to have the result of an IF be an equation, or rather the value to an equation. Anything I put inside the "" is taken literally and therefore I cannot enter an equation.

    For example, if the value in cell K11 is "yes" then the value in L11 should be the sum of I8+J6-J11.

    This is the actual formula I'm working on:
    =IF((AND(K11="yes", I8>=0)), "sum(J6+K6+K7+K8+J14-J11)", IF((AND(K11="yes", I8<0)), SUM(J14+J6+K6+K7+K8+I8), "0"))

    1. So I figured out my mistake, but now Excel is telling me I have too many arguments for this function. But I need to address all of these possible scenarios.

      Here's the new formula I have:
      =IF((AND(K11="yes", I8>=0)), SUM(J6+K6+K7+K8+J14-J11), IF((AND(K11="yes", I8<0)), SUM(J14+J6+K6+K7+K8+I8-J11)),if((or(k11="NO",L6<=0)), "$0.00"))

      Is there a way to simplify that Excel with accept, or do I need to find another way, like using two cells and separating my outcomes?

      Thanks!

  4. I have drop down list contains week numbers 1-54 and another two cells contain (week start date) and (week end date), how to show the week start and week end dates once I select the week Number in the first cell.
    (Week start dates listed in column A1:A54 as well as Week end dates list in Column B1:B54)

  5. HOW CAN I HAVE A FORMULA FOR THIS1. If cell (E260) is less than 20, times it by 2,
    if it is greater than or equal to 20 but less than 40, then times it by 3
    if it is greater than or equal to 40 and less than 60, then times it by 4
    And if it is greater than or equal to 60, then times it by 6.

    I CANT SEEM TO ENCODE THE BUT AND AND FUNCTION

    1. Hello,
      Please try the formula below:

      =IF(E260<20, E260*2, IF(E260<40, E260*3, IF(E260<60, E260*4, IF(E260>=60, E260*6))))

      You can learn more about Excel Nested IF in Excel in this article on our blog.

      Hope you'll find this information helpful.

  6. Hello Sir,

    I want to use if statement for two different excel sheets, is it possible?
    I want to replicate track list which is sheet no.-2, in sheet -1 only if shows any value. If not then need to disappear from sheet-1.

    Thanks

  7. I need to calculate a formula based on a Y or N in an adjacent cell.

    E2=A2*B2*C2*((IF(D2="Y",$H$3,IF(D2="N",$H$2)))

    D2 is a yes or no dropdown list.
    H2 includes a default factor.
    H3 includes an optional factor.

    E2=A2*B2*C2*$H$2 or E2=A2*B2*C2*$H$3 depending on the selection in D2.

    The value in H2 and H3 will be changing.

  8. If in the cell in column I there is a date, then the cell in column J needs to be one week later.

    ie.

    J10 = 9/18/2018 then K10 needs to = 9/25/2018

    This has to be simpler than I am making it, help appreciated.

    1. Chelsea:
      If cells J10 and K10 are formatted as Date, then the formula in K10 should read:
      =J10+7

  9. I need a IF statement that has a function that lets me hide row if it does not have an "AB or an "A". In the example below, I want to hide group "B" when they are by themselves on the schedule. I can't seem to get it so it hides the whole row... any help is greatly appreciated.

    START END GROUP ORG LP # LP NAME

    09:30 11:30 A FAD 600 Haptics
    B PTD 847 Cobalt
    12:30 14:30 AB BSD 400 Algorithms

  10. IN
    IN
    OJ
    OF
    OR
    WE
    OK
    OT
    TR
    how can find that, If cell value IN OF and OR, then shows for ABC else not

  11. Hai Gyus,

    Kindly help me below example...
    During employee working hours calculation...i got a error message in each cell...i.e. #VALUE!( in that login cell value is String and logout cell value is string = result is #VALUE!). If both value are string and result should be comes zero "0" either any other specific value....So could please help for error value...

    Thank you for timely supporting to us!.....

  12. Does anyone know how I would write a formula for this ? Gas Price Effect—The direction of the price of gas is a value from the Inputs section. If the price is expected to decrease, the effect is minus 5 percent (in other words, unit sales will decline by 5 percent in the year). If the price is expected to increase, the effect is plus 5 percent (unit sales will increase by 5 percent in the year). If prices are expected to be stable, there is no effect. Gasoline inputs are in cell B13 , D- decrease, U-increase, S- Stable. Units sold are in cells B31, and B32.

  13. Hi guys,

    I have a big challenge regarding conditional formatting.

    I want to classify about 400guys on their performance across 4 variables such as sales, costs, assets and profitability.

    1 scenario;
    Person that achieve >100% in 2+ variables and >=80% in the remaining should be classified Platinum.

    2 scenario;
    Person that achieve >80% in 2+ variables should be classified Gold.

    3 scenario;
    Person that achieve <80% in 2+ variables with a loss position should be classified Silver.

  14. Hi guys,

    I have a big challenge regarding conditional formatting.

    I want to classify about 400guys on their performance across 4 variables such as sales, costs, assets and profitability.

    1 scenario;
    Person that achieve >100% in 2+ variables and >=80% in the remaining should be classified Platinum.

    2 scenario;
    Person that achieve >80% in 2+ variables should be classified Gold.

    3 scenario;
    Person that achieve <80% in 2+ variables with a loss position should be classified Silver.

    See sample of the data

    Salesman Sales costs assets profitability
    1 78% 87% 78% 87%
    2 23% 50% 56% 50%
    3 37% 99% 88% 99%
    4 100% 89% 100% 89%
    5 123% 91% 123% 114%
    6 98% 89% 98% -23%
    7 78% 78% 78% 78%
    8 87% 77% 87% 77%
    9 94% 88% 94% 88%
    10 56% 89% 56% 89%
    11 77% 99% 77% 99%
    12 97% 90% 97% 0%
    13 59% 92% 59% 92%
    14 111% 84% 111% 84%
    15 105% 85% 105% 85%

  15. Thnx

  16. I want some formulae for calculating my taxes. The scenario is as below:

    First 3,300 is taxed at 0%
    Next 800 is taxed at 25%
    Next 2.100 is at 30%
    Balance is taxed at 35%.

    The works it works is:

    Gross Pay 6,000.00

    Calculation of PAYE
    First 3,300.00 0% -
    Next 800.00 25% 200.00
    Next 2,100.00 30% 630.00
    Balance (200.00) 37.50% (75.00)
    755.00
    It is not supposed to give a negative and if it is positive, the actual is supposed to reflect.

    Thanks

  17. i am wanting to create a formula for a large set of data where i am want to colour colde each cell that deviates from the figures in the 1st column like this

    negative shift
    lower by 1% - 5%, colour - light orange; lower by 6%-10%, colour - darker orange; lower by 11+%, colour - red.
    Positive shift
    Higher by 1%-5%, colour light green, higher by 6%-10% colour – darker green, higher by 11+% colour – dark green

    Equal with the figure in main column (no variance) no colour

  18. what formula is suit for if condition basing on year, like for example, we have cell A1 and B1, A1 value is 01/02/18 then we have to create formula for cell B1 which is like this, if cell A1=2018 then the value of cell B1 must be 1 otherwise 0,

    i just put a formula like this =if(A1=YEAR(2018),1,0) but it was error because the value of B1 showing after i enter it is 0, can you help on this?

    thank you.

  19. =IF((C4=A4,"P","O")=IF(ISBLANK(C4)=FALSE, "", "",)

    I have totally bungled my function.
    I want column d, which is where the function is, to stay blank until I input into column c.
    At the moment it is giving a false result even though I have inputted any data.
    Hope this make sense.
    Thank you,
    Catherine

  20. I'm trying to create formula where certain items are due at specific times over several days. I need the date to change once it turns over midnight. I'm assuming this is an IF THEN formula, but i can't seem to make it happen. Any ideas?

  21. how create formula for if sr no 01 get 1500 ,2 for 1000 and 3 for 500

    1. Shashikanta:
      You'll need to enter different text if the value in SR isn't 1,2 or 3, but the formula is:
      =IF(SR=1,1500,IF(SR=2,1000,IF(SR=3,500,"Something Else")))
      where 1,2,or 3 is entered into SR.

  22. I have 2 sheet, in first sheet I need to mention "A" or "P" on the basis of 2nd sheet where cell value is in time format HH:MM

    Please help to get t=it donee

  23. Chona:
    Because you have just five ranges to check, I think this will work. Where the value to check is in C29
    =IF(C29<49,0,IF(C29<59,"2%",IF(C29<74,"3%",IF(C29<89,"4%",IF(C29<=100,"5%")))))
    You can enter this formula in D29 and copy it down the column if you need to check values in C30, C31, etc.

    1. Thanks a lot, its working fine.

  24. If the total mark from M60 is 85 then in M61 must appear 5%; or if M60 is 61 then M61 is 3%. I cannot get my formula correct. please help. Thank you.
    Total mark is in M60 cell
    Point % is in M61

    Mark Point %
    0-49 0
    50-59 2%
    60-74 3%
    75-89 4%
    90-100 5%

  25. I need help.

    Lets say I have in column A a have rows with "yes" or "no", in column B values

    A1. yes B1 101
    A2. yes B2 102
    A3. no B3 103
    A4. no B4 104
    A5. yes B5 105
    A6. no B6 106
    A7. yes B7 107

    In column C I need only to see the "yes" positions, but I do not need gaps between rows, which means, that in
    C1 I want to see "101"
    C2 I want to see "102"
    C3 I want to see "105"
    C4 I want to see "107"

    Hope for your help :)

  26. I would like to ask a question :
    I have 4 text alternatives like ( Apple, Orange, Banana and pear).
    I want excel to check a cell and if that cell contains one of them do different sum calculation. for instance if it is apple sum up C1 and D5 and E3 and if it is orange sum C2 and D3 and E1 and put this sum in cell F1
    how can I do this ?

  27. Hi i am lookin to use the function below but it isnt working.

    =IF(B2 = I2,"YES","NO")

    Basically both cells contain text,

    So for example B2 would read "Dispatched" and I2 would read "Delivered" and the forumla would return the word YES

    If B2 would read "Dispatched" and I2 also would read "Dispatched" then the cell would return NO

    Its basically to see if the status of the booking has changed overnight and would be filtered to yer or no?

    Thank you for any advise or help?

  28. I am trying to create a tracking form for specific tasks with SLA's. In one cell, I have a specific task and in another cell the SLA say is "2" days.

    I am trying to get the SLA's to populate when selecting a certain tasks.

    Any help you can provide will be greatly appreciated.

    Thanks

  29. Any help would be appreciated. I cannot figure out how to make this work.
    If C5 = Y and D5 = Y then E5 = L. If C5 = N and D5 = N then E5 = H. If C5 = Y and D5 = N then E5 = M. If C5 = N and D5 = Y then E5 = M.
    Thanks in advance

  30. Logically, I want an excel function to check the value of week number and return the text string from one of Quarter1, Quarter2, Quarter3, Quarter4.

    For this, I'm trying to IF function to return the text string.

    The formula that I have used is -
    (Note-A3 holds the value of week number.)

    =IF(A3=13=25=37,"Q4","Please Check"))))

    The IF function works fine for week number values less than 13.
    However, for values 13 and above, it returns the text string Please Check.

    Kindly help diagnose the problem. And propose if any other function can be used to achieve the same result in a simplified way!

    1. Samrat:
      I'm not sure what you're looking for, but it might be this.
      =IF(A3=13,"Q1",IF(A3=25,"Q2",IF(A3=37,"Q3",IF(A3=56,"Q4","Please Check"))))
      A3 will have to hold these numbers for the Q value to be displayed otherwise the cell will display "Please Check".

  31. Hi.

    How can I make a formula who can do this
    content of cell A1 is 1 then display content of cell D1
    and if content A1 is 2 then display content of D2 etc.
    =IF(A1=1;D1) this works but only for A1=1 how can I put varios
    formulas together
    =IF A1=1 then D1, IF A1= 2 then D2 this one does not work.

    Thanks Rudy.

    1. Rudy:
      Are 1 and 2 the only values that A1 can hold?
      If so, would =IF(A1=1,D1,D2) work? It says, if A1 equals 1 then display D1 otherwise display D2.

  32. if a number 15 digits is correct but less then 15 digits number is wrong how to work out this

    1. Rajesh:
      If I understand your question you want to create an IF statement that checks the number of digits in a cell and returns one thing if it does and another thing if it does not. If that's what you're looking for the formula is: =IF(LEN(D35)=15,"Yes","No")
      This is for a digit count of exactly 15. Any digits more or less will return "No".
      Is that what you want?

  33. IF I WANT GREATER THAN 10 DIGITS AND LESS THAN 10 DIGIT NUMBER IN ONE CELL ?
    HOW TO APPLY FORMULA

  34. Hi,

    Below spreadsheet shows columns A-D and rows 1-9.

    I require a formula that states if A1 is blank & D1 contains text, insert a "B" in column A1 or leave existing values. Thanks
    A B C D

    1 3 K240 K240 - EARTHWORKS

    2 B K250 K250 - Clearing and grubbing

    3 K260 K260 - (a) Soft excavation

    4 3 K270 K270 - BASES 1 - 50

    5 K280 K280 - Blinding

    6 K290 K290 - Reinforcing

    7 K300 K300 - Formwork to sides of Bases

    8 K310 K310 - Concrete to Bases

    9 K320 K320 - Formwork to sides of plinths

  35. Hi,

    I'm curious if it's possible to use an IF function to compare text values.

    For instance, I download emails from one report then enter them in a cell next to the emails from an original report...I'd like to simply enter an IF function of some sort to compare them (as an automatic QC) rather than visually compare.

    Any help would be greatly appreciated!

  36. I need help in figuring out how to write a formula:
    I have four rows and twelve columns for my spreadsheet.
    Two of the rows will have whole numbers manually entered by me and my co-workers.
    I want to write formulas for the other two rows to do automatic calculations based on the data in the other two rows.
    Example:
    If the value of the data in cell A of row four is less than or equal to the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row four.
    But, if the value of the data in Cell A of row four is greater than the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row one.

    Numerical Example:
    Row 1 Cell A we manually enter the number 34
    Row 4 Cell A we manually enter the number 52

    Because (Row 4 Cell A) 52 is greater than (Row 1 Cell A)34, the value that should appear in Row 2 Cell A is (The value that is in Row 1 Cell A) 34

    or

    Row 1 Cell A we manually enter the number 42
    Row 4 Cell A we manually enter the number 36

    Because (Row 4 Cell A) 36 is less than (Row 1 Cell A) 42, the value that should appear in Row 2 Cell A is (The value that is in Row 4 Cell A) 36

    Once I figure out how to write this formula, I know how to write the formula for the cells in row three, which will be a simple subtraction formula.

    Can you help me please?

  37. It’s like a table 1 color print between 36 and 66 is .72

    1 color print between 66 and 144 is .97

    2 color print between 36 and 66 is .97

    2 color print between 66 and 144 is 1.15

    And it keeps going but I would like to populate the amount in one cell. So if I had a 3 color print and it was between 68 and 145 the price would be 1.15
    But if I had a 2 color from and it was between 0 to 68 then it would be 1.20

  38. I’m looking to do a formula . Having trouble with it. What I’m looking for is pricing. If the total units is between 56 and 144 and it is a one color print the price would be .75 but if the units is between 145 and 200 and it is a one color print the price would be 1.00. Can anyone help me with this?

  39. Bayasaa:
    I think this will work for you:
    Enter this in D1:
    =IF(AND(A1="Local",B1="Temporary",C1="Medium"),"Low","Not Low")

    1. It’s excel online so I’m going to guess the most up to date one.. it just frustrating when I’m doing a quote at work that I have to keep looking at the sheet and there is more then one sheet like this so I wanted to put it in excel since that is where I build my quotes.

    2. Oh and that table is 8 x 8
      Up to 8 color prints and 8 price range

    3. No I have never used either one of them before.

  40. Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low". how can i enter the formula in d column?

  41. Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low"

  42. Help!

    Column A Column B Column C Column D Column E
    ABC DEF
    ABC ABC

    In Column E, I'm trying to find a formula that would populate ABC when it's only ABC in one or more cells in row 1; DEF when it's only DEF in one or more cells in row 1; if ABC and DEF are in the same row as it shown in row 1, then I want the formula to return "BOTH". IF statement doesn't seem to work IF(A2:D2="ABC","ABC",IF(A2:B2="DEF","DEF",IF(A2:A2="","","BOTH")))

  43. Help!

    Column A Column B Column C Column D
    ABC DEF IF(I2:M2="ABC","ABC",IF(I2:M2="DEF","DEF",IF(I2:M2="","","BOTH")))
    ABC ABC

  44. IF(A12.5,"1500")))). CAN YOU PLEASE GIVE ME CORRECT ONE...

  45. =IF(A4="GP","3",IF(A4="DMF","4",IF(A4="CARD","5",IF(A4="ORTHO","6"))))

  46. SUB_LEVEL IN_DATE WORK_DESCRIPTION STATUS OUT_DATE
    A 17-07-2018 SKELETON MAKING #VALUE! 18-07-2018

    1).In STATUS Column i want "In Progress" when i enter date in (IN_DATE) Column.
    2). And i want "Pending" in STATUS Column if the SUB_LEVEL Column is empty.
    3). And i want "Finished" in STATUS Column if i enter any date in OUT_DATE Column.
    I have tried using the following formula but it returs an error, Please help:
    =IF($B4"","In Progress","Pending")*OR(IF($E4"","-Done",""))

  47. I would like to create a formula that enable me to do the following

    If col A =Jack is selected (from a list of 10 names) and
    Col B=Cat is selected (from a list of 10 animals)

    Col C= result in a date (from a list of dates)

  48. Hi,

    I have 4 Column, In column A Date, B diffrent Product (almost 10)
    C Purchase Price D Sale Price.
    Daily sale / purchase enter one by one.

    I Want Prouductwise total (Purchase / Sale) in sepate sheet or column

  49. we need a more clear formula for this function

  50. 100 direct 70 Indirect
    if direct method shows 100 value than 70 shows indirect method ?kindly help me

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