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 42. Total comments: 4822

  1. I am pulling my hair out on a formula. I have a workbook with 5 tabs ( Gen212, Poe,360 BWS, sample, Production). What would the formula be to have a flow number from tab 5 (production) in column H row 7 automatically populate into tab 3 (360 BWS) in column C row 18?

  2. Can someone help with formulas for the following 2 scenarios:
    1. Groups submitted from the 25th of the previous month
    -For month of February: statement is true if the date is 1/25/19 thru 2/28/19
    2. Groups submitted from the 25th of the current month
    -For month of February: statement is true if the date is 2/25/19 thru 2/28/19
    Thank you!

  3. I have two columns. Column G is my due date and column J says whether I have approved or denied the application. If the due date is in the next 7 days, from today, and the corresponding cell in column J is blank (meaning I have not approved or denied the application) I want the cell in column G to highlight, or the row, to let me know the due date is coming up and I have not taken action on the application yet.

  4. Hello-

    I am in desperate help of trying to figure out an if statement that can help me calculate # of days past due.

    I have a column for start date, end date and actual date of completion. I have a column with a formula for today’s.

    Originally, I used the networkdays formula to calculate the days from due date to today’s date, but when I put in the actual completion date, the past due days keeps updating and I don’t want that.

    Have no idea if that made any sense. But hopefully someone can help me out !!

  5. Hi,
    May i Know how to use TODAY function when my table has different dates,
    Date-1, Date-2,Date-3,
    How to get the same data on one column based on IF Function,
    thanx

  6. Hi,

    I want to write a function that will find alternate minimum value for a range where logical value TRUE equal 1 and the logical value FALSE equals 0, and the text and blanks equals 0.
    Thanks,

  7. G 12 is an unknown which is a value where i keyed in, meanwhile E12 is a value which i obtained through a returned value from another keyed in a value, both G 12 & E 12 multiply each other and should not give you more than 220, if it does, it supposed to print out 220 or else the value is supposed to be the the equation provided,need some help as i am getting errors when executing it

  8. Problem getting IFS formula to work.
    Problem I want the formula to read the cell and if it contains a certain text,then it is to input certain text, and if it is another text,then input another text,if the cell contains a certain text,then input a certain text.

    Example:
    Cell: =IFS(C1="CAR","A.V",C1="NQR",C1="SCAR")
    Formula only regonizes CAR and inputs A.V.,but will not work for other two conditions.

  9. Hi Svetlana,
    How would I write a formula for time zone conversion sample:
    Cell D2 Local Time, E2 is the GMT Conversion and L2 is the Converted TIME Zone
    is it like; =IF (E2=+2), D2+600
    My logic is: The time in L2 is the equivalent of D2+600 if E2 has value of +2
    Hope you could reply.

  10. What my goal is to have a set of cell content only (B4:K6)to empty at 6am daily. Is it possible and if so how do I accomplish this task?

    Thank you.

  11. I'm trying to use this formula for these three qualifiers. Can't get it to work. Any suggestions?
    =IF(AND(ToCSDIncidents[@[Assignment Group Final],"CSD_SERVICES",ToCSDIncidents[@[Reassignment Count]],"0",ToCSDIncidents],ToCSDIncidents[[Exclusion]],""),"TRUE","FALSE"))

  12. =IF( ((G12 * E12) + 14)> 220), 220, ((G12*E12)+14)
    Hi everyone i need help on these if statement on excel, G 12 is an unknown which is a value where i keyed in, meanwhile E12 is a value which i obtained through a returned value from another keyed in a value, both G 12 & E 12 multiply each other and should not give you more than 220, if it does, it supposed to print out 220 or else the value is supposed to be the the equation provided,need some help as i am getting errors when executing it

  13. that looks at the contents of a particular cell (either Global Brokerage or USA Brokerage) and then, depending on Global vs. USA, I want to point to a 1 of 2 cells that contains a percentage. The percentages can change and I do not want to type the actual percent into the formula. I want to point to the % in 1 of 2 cells.

  14. Please assist me as I am trying to create if then function in excel but I don't know how to include if the cell is blank =IF(K2335="eamc","YES","NO") but this statement also put "no" if the cell is blank. how can I make it like if there is no data then it has to be blank as well.
    Thanks

  15. Hello,
    I have tried several ways to do this,please help.
    if A2:21 says complete it equals a % and totals all within
    Thank you

  16. I have an simple If function in B1 for example =IF(A1>=1,"1","0").
    The entry number works if I use it in a =SUM formula horizontally in D1 =SUM(C1-B1). Example: A1 entry is 2, B1 becomes 1. C1 entry is 4, D1 becomes 3. Works great.
    Here is where I run into trouble. I copy formulas vertical example: B2, B3, B4... then attempt a simple SUM formula =SUM(B1:B4) the result is always 0 even if B1 thru B4 all have 1 as a result.

  17. IF Cell A1 IS NOT EQUAL TO Cell B2 or Cell A1 is blank then it is to be considered as Cell C1 data

  18. Hi , may I seek some helps on excel function... is there a way for the excel function to do an automatic recalculation if a defined criteria is not met..like IF B2=5 or less than 5 then goto do recalculation..
    Thanks and best regards

  19. I am trying to setup a formula that looks at the contents of a particular cell (either Global Brokerage or USA Brokerage) and then, depending on Global vs. USA, I want to point to a 1 of 2 cells that contains a percentage. The percentages can change and I do not want to type the actual percent into the formula. I want to point to the % in 1 of 2 cells.

  20. Hello,
    I am trying to add a column of IF statements and the sum will not recognize any number other than 0. My IF function in each cell is:
    =IF(H91>14.9%,"3",
    IF(H91>9%,"2",
    IF(H91>8%,"1",
    IF(H91=0%,"0"))))

    How can I sum this column automatically?
    Thank you.

  21. Hi, can you Help me to solve this, i want to create 'A,B,C and D' for following marks
    A=90 above
    B=80-89
    C=70-79
    D=60-69

    please help me to create the formula using IF function

  22. Hi! I need a formula for the following: If A1 or B1 or C1 or D1 contain "2" then write "True" in cell E1. I would like to know that any of the mentioned cells contain number "2". Thank you for your answer in advance! :)

  23. Hi Everyone,

    i need some help in replacing the column text.
    Scenario: A3 needs to be replaced in new column J3. If it matches with the values in (B3 = H3 AND J3 = C3) when the data between these column matches i need to replace the text value from A3 to J3 else "NA". Can someone please share the logic that would be much appreciated!!

  24. Here is the scenario. I am currently counting down sizes for tshirts once as someone purchases one. I want to say if Any Row 1 to 200 on column D contains an "XL" then minus one from 100. How do i write that? So far i have =IF(D1="XL",E546-1,"") and it is just working for the cell D1 only. I want it work for the entire D column. Please note i have the 100 on E546. Help please

  25. Hello i am struggling on what formula am i going to use in order for me to put the value of a cell using some reference, example,

    if A1 CONTAINS 12345678 THEN PUT B1 ON THIS CELL. Can you understand the situation? please help

  26. I am trying to write an IF statement for a gym membership. Column C5 is the cost of the membership and Column D5 is the extra cost for a locker. If there is a YES in D5, add $75. If NO, no extra cost.

  27. I need something this type of answer in excel.. Please help:
    I want answer as "Sensitive" or "Insensitive" as per the texts in the reffered cell.

    exp: =if(A2= 376/377/376A/376B/376C, "Sensitive", "insensitive") .. hope you get my Idea what I am want in separate cell ?

  28. How to I call to both numbers and have text in my if statement. I want to say something along the lines of "You are " + x + " dollars over budget". With the X being a called variable able to be changed.

    Thanks y'all for your help

  29. Values to appear
    - to 10
    11-20
    21-30
    Above 31

    How can we use this in IF Condition as a status.....

  30. My total due amount is in cell A1 and status in cell A2
    i want cell A2 to change itself as per my conditions
    For ex,
    If A1>=0 then A2 should be "PAID",
    If A1 is greater then 0 but less then 350 A2 should be "DUE",
    & If A1 is greater then 350 A2 should be "OVER DUE"

  31. I'm trying to write a formula for the cell to display "x" if the date is greater than TODAY and if the cell does not contain any text.
    Tried this and I can't get it to work:
    =IF(AND(N118>TODAY(),N118*txt*),"x","")
    Any suggestions?

  32. maya has 2 car no DD03F9581 AND DD03G9344. AND LALU HAS 1 CAR NO DD03F9702
    WHEN I TYPE ANY OF CAR NO IT SHAW OWNER NAME BY FORMULA.

  33. Hi, I am trying to write an if statement to return the value of 0 if the number in column c = 38 and if the number in column c is not 38 return the value in column O in the same row.

  34. Ihave 3 column "Amount" "Norm" "stab"

    Amount is => norm result is *Norm*
    Amount is => stab result is *stab*
    Amount is 0 result is *unpaid*
    Plz suggest

  35. I want a formula to calculate a value into column D
    The one I have will not return a dollar value. What am I doing wrong?
    A B C D
    10 10.00 #VALUE! S/B 11.11
    20 10.00 #VALUE! S/B 12.50
    30 10.00 #VALUE! S/B 14.28

    =IF(A2:A4=10,C2/0.9,IF(A2:A4=20,C2/0.8,IF(A2:A4=30,C2/0.7)))

  36. Hello,

    I'm trying to set up a sheet to keep track of cleared checks. I have a column that has the check amount (Col B). I have a check box to indicate it if was cleared (Col J). I want to have a column for the calculation (Col I). So if the check is Cleared (True) then this Column shows 0. If it is not cleared (False) then it shows the same amount as Col B.

    So far I have it to working with the True. I can't get it to equal the value of another cell for false though. I'd appreciate any help)

    I'm only trying it on a specific cell to make sure it works first.
    =IF ( J5 = TRUE, "0",(=B5))

  37. Hi all,

    I am trying to pull data from 3 different tabs onto one sheet. The data are US states in the columns and the other data is either "yes" "no" or is a blank cell. I've done vlookups to match the y/n/blank into 3 columns on one sheet. (I'm sure this isn't the best way to do this either..)

    But now I'd like to pull the data from those 3 columns (each pulling from a different tab) and do if statements to pull my answer into one column. So, for example, State = AL, B1 = yes, c2 = blank, c3 = yes. How do I write a formula to look at all 3 and pull which column isn't blank?

    Thanks in advance!

  38. how do write fourmela if cell 1 = cell 2 then add whats in cell 3

    i have 2 lists 1 of username and pc name and the other one is username and phone number so i put all to the same file i want to make if the name match the name then add the phone number so i can have pc name username phone number

    thanks in advance

  39. Hi,
    Would someone help me with this assignment, it is urgent!
    We want to find the average grade but if i have all A and only a F in the average grade the argument should be "fail" (maybe this is with "IF" function) and i don't know how to do this.
    Hope i was clear.

  40. How do you write an IF formula for a question and answer sheet?. Example of sheet would be Line 1 the Question and line 2, 3,4 & 5 would be the answer following a check box next to each answer. When checking the box it would show True or False at line 6.

  41. I need help with a task.
    I have three columns, One has a list of vendors and if that vendor was on our job that month I put a Y in the 2nd column next to his name. The 3rd column then will have another Y if that vendor has turned in his paperwork for the month. What I want is if the 3rd column is blank i want to highlight it. But if there is No Y in the 2nd column then I want nothing done for those cells. So in other words, if the vendor wasn't onsite then I don't care if his paperwork is in. But if he was and he hasn't turned it in I want his cells highlighted.

    Thank you for your help.
    Jennifer

  42. I need your urgent help please
    I have two column, i want the current date in second column if i put a text in first column,
    When there is no text in first column then no date should be in second column

    1. Ahmed:
      From your post it's not clear what type of text should be in the first column, so I just used ISBLANK. Here's what I came up with for you.
      =IF(ISBLANK(A2),"",TODAY())
      The cell where this formula is entered displays today's date when A2 is not blank otherwise the cell displays nothing.

      1. Doug,

        regarding the =IF(ISBLANK(A2),"",TODAY()) , Will the date automatically update each day or will the date be set to whatever day you saved the file? I am trying to use the same idea. I want the date to post when anything is typed in cell A2, but I dont want the date to automatically update each day. I have to create a single file for each day of the month and am hoping to not have to go into each file and type the date each time. Looking to create a template.

  43. Is there a formula for changing a cells color? Conditional Formatting will not work for what I want...

    I have a block of text, i.e. A2:F100, (the amount of rows changes every week) in which column A holds the different "Tape Numbers" (AA0053) that I'm verifying. I copy in a column of current available scratch tape numbers in the H column.

    What I want is for my formula to look at H2 (AA0008) and search column A. If it finds AA0008 then color fill H2 Green, IF NOT then color fill H2 Red. Then I'll copy that formula down to H3 and so on... I know the =IF statement will do the True/False check, but I'm not sure how to tell it to change the background color.

    A B C D E H
    AA0008 *EXP 11/19/2018 11/27/2018 11/27/2018 AA0008
    AA0058 *EXP 7/21/2013 7/29/2013 3/16/2015 AA0009
    AA0085 *EXP 7/10/2018 7/25/2018 7/28/2018 AA0010
    AA0086 *EXP 9/1/2018 11/30/2018 11/29/2018 AA0011
    AA0105 *EXP 12/14/2018 12/22/2018 12/22/2018 AA0012

    Conditional Formatting only find the "True" statement then formats the cell. I also need the false, so I want an "old school" formula. I'm not well versed with VBA, so I'm hoping to stay away from Macros.

  44. My qustion is that in a cell in my worksheet which contain only date (12/12/12 to ...) and n/a so I need comment of dateee no problem,or n/a it should be a problem...

  45. I am facing a problem in excel sheet I need your help.
    For example if we enter the value like this
    1517001255 (A6)
    To (A7)
    1517001260 (A8)
    Than how we can find the (1517001256,1257,1258,1259) in H6
    Waiting.

  46. Please if you can help me my IF format is

    =IF(C2 ISBLANK,"",+30DAYS

    So basically what I want is if there is a date in cell C, then for cell D to add 30 days but if it is blank then leave cell D blank, i have an error in the +30 days but i can not work out how to do it

    1. Hello, Sam:
      I think what you're looking for is something like this:
      =IF(ISBLANK(C2),"",C2+30)
      Be sure C2 and the target cell are formatted as dates.

      1. Thank you so much Doug :)

  47. MF:
    There are several ways to identify duplicates in Excel. One way is to use Excel's built-in duplicate identification tool. Check out this article in AbleBits for several methods to accomplish your task.
    https://www.ablebits.com/office-addins-blog/how-to-highlight-duplicates-excel/
    When you want to look across several sheets you can use something like this in the formula =COUNTIF(Sheet2!$A$1:$A$7,A1)>0
    Where this is in the Sheet1 conditional formula rule.

  48. I'm having all sorts of problems with this. Please help!

    First tab: Typical Business Marketing List of 1200 businesses
    Column 1: Company Name
    Column 2: Address
    Column 3: City
    etc.
    2nd tab: Narrowed list of 50 of those businesses for a targeted mailer.

    I'd like to add a column to the main tab that indicates which businesses on the 2nd Tab were sent the mailer, with an X or something, perhaps. To do this, I have to somehow match the two columns on the different tabs and then if there's a match, an X will appear.

    I've tried =IF, =MATCH, VLOOKUP with a hundred different options and I can't figure this out.
    Any help would be greatly appreciated!!
    Thank you!

  49. Hii, am having a challenge with using an IF function to filter data of the type: 12/4/2018 5:53:40 PM.
    I have got a date captured just like above together with the time and i would like to filter by the date.
    How can i go about this?

  50. I am trying to increase vacation hours by 3.5 hours each year after 10 years. So far I have it working for 3 yrs, 4 yrs and 5
    =IF(A2>=2016,105,IF(A2>=2015,140,IF(A2>=2012,175))) not sure how to continue to include and increase by 3.5 each year after 2009

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