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

  1. Hi,

    Please help me out with the excel formula for Gosi (government tax) calculation.

    Gosi - =(Basic+HRA)*Gosi but I want add saudi and non saudi in the excel sheet. So if the column has saudi employee will calculate accordingly and if non saudi as per that.

    Eg:-

    Saudi Gosi Company Expenses - 12% and Saudi Gosi employee expenses - 10%.
    Non Saudi Gosi company expenses - 2%.
    And below are details and please show some formula for the same.

    Nationality Basic HRA Gosi Comp Expenses Gosi Emp Expenses
    Saudi Saudi 10000 5000 12% 10%
    Jordan Non Saudi 7000 3000 2% No deduction
    Yemen Non Saudi 3000 1000 2% No Deduction
    Pakistan Non Saudi 2000 500 2% No Dedection

    Thanks Hooping Soonest reply.

    Regards.
    samsher Hashmi.

  2. Hi - I have an "IF" formula that I want to sum up but it is giving me "0" even when the "IF" formula's have a total.

    =IF(D4>1,"1","") this goes from A4 to A 103, I want A3 to be =Sum(A4:A103)

    What am I doing wrong? :(
    Thanks!

    1. Hi Rebecca,

      To add up cells that meet a certain criteria, use the SUMIF function like:
      =SUMIF(D4:D103,">1",A4:A103)

  3. I have a table and a list box. I am attempting to write a formula that takes the list box value and changes the table values based on the list box, basically resetting the table.

    The table is set up for people to enter values. Then they need to be able to 'clear' the table and start over. We are not able to use macros or vb.

  4. HI,
    KINDLY HELP ME IN I HAVE A RANGE OF DATES VARYING FROM 0 TO 60. I NEED TO CATEGORIZE INTO FOLLOEING GROUPS IF DATE RANGE IS BETWEEN 60 TO 30 IT SHOULD RESULT > 30DAYS, 15 TO 30 AS > 15 DAYS, 5 TO 15 AS > 5 DAYS AND 0 TO 5 AS < 5 DAYS..

    =IF(OR(I25,"<5 DAYS",I2=30,">30 DAYS"),">DAYS")

    1. Hi KARTHICKEYAN,

      You can use a nested IF formula similar to this:

      =IF(I2>30, ">30 DAYS", IF(I2>15, ">15 DAYS",IF(I2>5, ">5 DAYS", "< 5 DAYS")))

  5. Hi I am unable to figure this out. I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding [In Production]1" cell.

    Many Thanks

    1. Hi Martin,

      Try a formula similar to this:

      =IF(COUNTIF(A1:A10, "1 In Production")>0, "In Production", "")

      Where A1:A10 is the range of cells to check for the term "1 In Production".

      1. Works perfectly!!!!

        You saved my life!!

        Thank You

  6. Hi,
    I have 3 columns (A, B, and C) and I want to make a list of text of column A based on the conditions of column B and C. It is somewhat like "if B = "text" and C = "text" => copy the text in cell column A and paste to the new cell.
    How is the formula for that?

    Thanks.

    1. Hi Karissa,

      You can use a formula similar to this:
      =IF(AND(B1="x", C1="y"), A1, "")

  7. hi..
    this lesson was more helpful for me to study regarding functions of MS excel.thanks a lot all of you..

  8. I am looking for a formula that will compare 2 cells on a sheet, if they both contain the letter o then the number 25 is to appear in a third cell, if one or both cells are blank, then 0 is to appear. i.e. if cell AE6=o and cell AE20=o then cell AF6 is to say 25. If either cell AE6 or cell AE20 (or both) are blank then cell AF6 is to say 0. Please help, thank you.

    1. Hi Chris,

      You can use a nested IF formula like this:

      =IF(AND(AE6="o", AE20="o"), 25, IF(OR(AE6="", AE20=""), 0, ""))

      1. Thank you. I am also looking for a formula that will post a symbol if 1 of 2 different cells = a value of 1, and fall within a of a number generated in another cell. Here is the formula that I am trying to use: =IF($E$7=1,IF(AND($L$18>=190,$L$18=160,$L$18<=164),"•")),"")))
        Let's say that this formula is in cell A1, and when E7 is "activated" by equaling 1 a random number is generated in cell L18. If the number in that cell falls between or equals 190 and 194, I want a • to appear in cell A1, if not, then blank (unless the following formula is true). In the same cell A1, when D6 is "activated" by equaling 1, a random number is generated in cell L18, and if that number falls between or equals 160 and 164, I want a • to appear in cell A1, if not then blank (unless the other formula is true). Both E7 and D6 will never be "activated" at the same time. My current formula returns a #VALUE report in the cell.

        Thanks in advance!

        1. Hello Chris,

          Please use:
          https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/

  9. please suggest , if i select any month i need date wise in row

    If May is selected then date 1 to 31 ,Sept 1 to 30 and for leap year feb

  10. Hi,

    I want to set up a formula that calculates if an invoice is "overdue" or "OK".

    I have my Invoice Date Raised column, I have a Due Date column and a STATUS column which I currently enter manually. I want a formula that will change a the STATUS field as the Invoice date is hit/over. Does anyone know if this is possible and if so how? Im guessing I need a field to show the current date?

    Michelle.

  11. HI

    I have a problem where I have to write an if function for "if the last digit in a number is even enter 1000 if not, 0". I can't figure out how to put the "last digit is even" part into the formula to make it work in excel.

    Thanks in advance

    1. Hi Peter,

      You can use the RIGHT function to get the last digit and ISEVEN to find out whether it's even. For example:
      =IF(ISEVEN(RIGHT(A1,1)), 1000, 0)

  12. Hi,

    I want to do a if function to a data range (not just one cell). For example, for data ranga A1 to Z1, I key in =if(A1:Z1=2,"1","0"), but of course this is incorrect. Can you help me get the right formula or function for this one?

    your help is much appreciated.

    Thanks.

  13. Hi,
    I have several columns with dates (in ddmmmyy format). I want to determine which date in a row is the most recent and determine how recent it is by comparing it to today's date. How do I do that?
    Thanks in advance,
    Henry

  14. (6516513) <<>> (6516681321)

    this two codes are different but meaning is same. Is this possible? ( if I could teach to excel that this codes are same and if it is possible to create formula which will find (using vlookup or other formula) one of this code give him some colour and same time this formula gonna find second code which is same and gave second ones same colour)??? in case of every other code colour must be different :(

  15. Hi,

    can you please help me to pick the start and end date of following excel.

    WO CO start End 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep
    11856 MK1501 30
    11857 MK1502 60 60 60 60 30
    11858 MK1503 30 60 60 60 60

  16. =IF(F9:F10<0,'1A'!P3:P3+'1A'!F9:F10,MASTER!P3+1)
    Please help me for adding two column cell in formula

    Thanks

  17. I have cells c6:t6(achieved scores of students) but the obtainable score is on c4:t4. that is, if in cell c6 a student scores 0 but the obtainable score on C4 is 25, meaning they score 0 of 25, D6=3 and D4=3 (meaning 3 out of 3). I want U6 to be the sum of obtainable scores.

  18. Hi,

    i have a lot of machines need annual certification, and i neeed to apply if function to let me know
    if the date of certification "EXPIRED" or "VALID" or will be expired within one month

    Thanks
    Abbas

  19. HI,

    So i have A1 - D1 as breakdown values for total in E1 and;
    have 'Paid' in F1 and the date in G1 and;
    have the same amounts as A1 - D1 in H1 - K1 if its paid.
    what i want is to automatically have the amounts in A1 - D1 in H1 - K1 if i type in Paid in F1.

    Can you tell me a IF formula i can use for this?

  20. Actually I want any formula To I Used in excel To sort out data Like We have 3 Column SIZE DIA and Buyer Code
    Buyer Code is F-015 This buyer want Many Sizes different Sizes different dia 41.5 dia is 150X200 29 Dia is 100X200 and 47 Dia is 180X200 So i want any condition i put Buyer code and Dia auto detect its size

  21. hi friends i want a something mentioned below

    if G5 between 90 and 95 then Pass other wise Fail

    Please help

    Thanks

  22. Can your "value_if_true" or "value_if_false" statements in an IF function reference pull text from another cell?

  23. Need for for calculating if age falls within a certain range then to return a set figure. I have generated this but its giving me no joy:

    =IF(AND(B6>=18,B6=31,B6=51,B6<=64),3.5)

  24. Hi,

    I have 2 columns with dates in them.
    A=Current Due Date
    B=Reschedule To date

    How can I do a logical test to show in a 3rd column all the reschedule dates are more than a week apart from the current due date?

    Thanks,

  25. Hello,
    I want to create a formula , for to look in coloumn B2:B100, in cell text contain is "Raj" then print "Raj availabel" in A1 cell

  26. hi,

    I have data tabke

    date time open high low close

    and I want to convert the data for a day/days into time ranges so that I can chck how it moved with each day as a unique day.as the O H L C will chane with in the day only as er time change requested

    If I wat to pull the 1st open for a time range, how do I pull the 1st open even though it is not the start time In my report sheet.

    it may so happen that the data may start coming only 2-3 seconds later though say at 9:00:03 where as the start time in my query would by 9:00:00

  27. HI,

    HOW WOULD I WRITE THE FORMULA TO GET THE FOLLOWING

    IF a0,IF(A3="INDIA",6,IF(A3="NEPAL",10,IF(A3="SRILANKA",15,))))),"")

    LEADTIME
    INDIA = 8 DAYS
    NEPAL = 10 DAYS
    SRILANKA = 15 DAYS

  28. Hi,

    How would I write the formula to get the following:

    If A10.99999,A1=0.99999 otherwise = A1.
    I want to Winsorise it to be between 0.00001 and 0.99999

    Thank you for your help!

    Nazi

  29. I want to create a formula. I have 5 types of value like 4.17, 4.33, 4.50, 4.67, and 4.83 I want if 4.17 comes in a cell the dependent cell will show 4 min 10 sec. For 4.33 it will show 4 min 20 sec, 4.50 show 4 min 30 sec, 4.67 show 4 min 40 sec, 4.83 show 4 min 50 sec and 4.00 show 4 min. How can I do it in excel?

    1. the value before decimal is min like 6.17 will represent 6min 10 sec

  30. I have an excel question. I've got a scenario where I'm trying to write and if/or function. Here's what I have so far: =IF(OR(C43=0,C44=0),0,IF(OR(C43120,C44>4),0.15,0.1))). If C43 is between 1-60 or C44 =1, then the discount is 5%. If C43 is between 61-120 or C44 is between 2-4, then the discount is 10%. If C43 is greater than 120 or C44 is greater than 4, then the discount is 15%. Can you help me write this formula? Thank you!

  31. A B C D E F G H
    Annual Q1 Q2 Q3 Q4 Annual Q1 Q2
    1]3.8 1.08 2.06 0.15 1.1 1.1 1.08 0.89
    2]3.8 1.08 0.98 -1.91 0.95 1.1 1.081 -0.183

    Hello sir/Madam
    I have a question in above situation.
    1st condition is Annual & Q1 value be always match . but Q2, Q3,& Q4 value never be match.
    if Annual & Q1 value mismatch then it should be an error, and if Q2, Q3, &Q4 match then it should be an error.
    now i have try [if(A1=A2,"OK","error")]this formula. but this formula work only Column A & B (e:g Annual & Q1)but it does not work Q2,Q3,Q4
    So, could you please help me in this matter....
    Thanks
    indranath Bag

  32. Hi,
    i need a sugestion for this formula,
    please look at this
    =if(E5>=D5, "E5-D5", " ")

  33. Plese correct this:-
    =IF(A1>=A,"5",IF(A1>=B,"4",IF(A1>=C,"3",IF(A1>=D,"2",IF(A1>=E,"1")))))

  34. I J K L M
    Year Month Cycle Month Leave Due 4month
    2010 5 10 14.67 14.67
    2009 11 10 14.67 14.67

    M - Formula: =IF(I3>2013,IF(J3>5,0,L3),L3)

    What are they trying to determine to get to 14.67
    This is a provision for leave days

  35. Hi,

    I need help in calulate how many time my staff take MC for the month.

    I try this formula,
    (=IF(N28="MC","1")+IF(N29="MC","1")+IF(N30="MC","1"), its work but I have to key every cell for the whole month that will be very long, any way to shorten it.

    My range is from N4 to N38

  36. Hello,
    I have a sheet which serves as my database with daily information and I want to create a summary sheet using data from the database sheet (DB). So what I want to say is if column A (from the DB) is today's date and Column B (from the DB) is Summer2015 then give me the figure (total visitors) of column C (from the DB) into a new sheet to create my summary sheet. I want to do this process using the past four years of data to compare what the total visitors was today compared to the date last year. So if today (2015)I had 1000 visitors, last year September 1, 2014 I had 600 visitors, 2013 August 3 I had 1500 visitors. I have other data I want to compare but if I can help with this one I can do the others. Thanks.

  37. hi,

    wanna print the entire row if cell value has as part of string.
    say if cell A2 has "421." want to print entire row 2. actually value of A2 is "421.52.4587"

    Thanks in advance

  38. hi,

    wanna print the entire row if cell value has as part of string.
    say if cell A2 has 421

  39. I have 4 columns
    A= Number of registrations (33)
    B= Number of errors (1)
    C= B1/A1 which equals 3% (formatted as a percentage)
    D= sum(1-C1)which equals 97%

    How to I get rid of the #DIV/0! if A1 and/or B1 = 0 (zero) ??

    Frustrated,and hoping you can help.

  40. looking for code for an Excel IF THEN where
    if H11 says "Customer Pick Up" or "Delivery" then Rows 59 through 62 are hidden

    if the value of H11 is anything else it can remain unhidden

    Thank you in advance for your help!

  41. Is there a way to convert the string "2015-08-26T14:22:27.306-04:00" to a readable date and time by creating a formula to ignore the T, and convert the date and time? It is not feasible to do this by hand as my data is in the hundreds, and grows everyday.

  42. Hi. I just wondering what is the formula to get Overtime Hours. The Normal hours is 8 and the Overtime Rate 1.5.
    Hope you can help me with this.

    Thank you.

    Louie

    1. I'm having problems trying to figure out the first name, last name, hours worked per day on a five-day workweek, with hours. worked 45, hourly wage $9.75, overtime hrs.5 at a time and a half, and the total earnings.please help me, I will be forever grateful.

  43. i have cell A1 with value 1.5.
    i want a value in cell A2. if value in cell A1 is less than 0.8, 0.8 should come in A2, but if value in Cell A1 is more than or equal to 0.8, same value should come in A2. like in my case 1.5.

    i tired this =IF(A1<0.8,"0.8","A1"), but its not working. i am getting text A1 in cell A2 not value 1.5

    please reply

    1. Hi Rahul,

      In Excel formulas, numbers and text references are not supposed to be enclosed in quotes. As soon as you remove them, your formula will work fine:
      =IF(A1<0.8, 0.8, A1)

  44. Dear Admin, I want to ask for help

    Here is my condition:
    - i have a table A to get the status "YES" for the first condition
    - i have a table B to search the value-text within, which is the name of the employees
    - and the third table C as a result
    I put this formulas at C that i wish to get 1.5 if "2 condition met" and 0 if "false"
    =IF(AND(H4="YES"),IF(ISNUMBER(SEARCH("name1",row1)),0.15,IF(ISNUMBER(SEARCH("name1",row2)),0.15,IF(ISNUMBER(SEARCH("name1",row3)),0.15,IF(ISNUMBER(SEARCH("name1",row4)),0.15,0)))),0)

    The problem is: when i add one more row (row5) it always give me "you've entered too many arguments for this function". Is there any other possible formula for me?

    Thank you very much

  45. Hi id like to create a formula using If condition wherein you have three ..
    For example

    I use =if(or(c27=40,c19*c26,c27=60,c20*c26,c27=80,c21*c26)) how can i checj this?it is always error

    1. Hi Grace,

      Use a nested If formula like this:
      =IF(C27=40, C19*C26, IF(C27=60, C20*C26, IF(C27=80, C21*C26, "")))

  46. Hi
    I want a formula to add several cells if they have a value say greater than £0.01. say cells B5 to B10 have a value greater than £0.01 and cells B11 to B13 do not have a value. so the count should be 6 and the cells that do not have a value turn red.

    Thank you.

  47. Hi
    I want a formula to add several cells if they have a value say greater than £0.01. say cells B5 to B10 have a value greater than £0.01 and cells B11 to B13 do not have a value. so the count should be 6.

    Thank you.

  48. i have a price list in sheet number 1 and an invoice in sheet number 2
    and i want to make an if function to help me when i write the code in the invoice the excel bring the price from the price list sheet.

  49. Thanks Svetlana, that works for my intent and purposes with a bit of mods. Now if you can help me with one more....
    I have 2 sheets containing about exact same data, but Sheet1 has several cells in Column B highlighted in Blue. How can I run a formula to color the matching cells on Sheet2 to be blue?

    1. Hi Jay,

      If you want to highlight cells on sheet2 that have the sames values in the same cells on sheet1 (regardless on cells' background color or other formatting), you can create a conditional formatting rule with the formula similar to this:
      =$B1=Sheet1!$B1

      For the detailed step-by-step instructions, please see:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

      If you want to highlight cells on sheet2 corresponding to blue cells on sheet1, there is no formula for this, and the only way is writing a VB script.

  50. Hi guys, extremely helpful site. I have a simple one. I have a sheet with several names in say Column B. Column C is looking for the City. If Sam is found in Column B, return Toronto. If Joe is in Column B, return London. Sam, Joe and the other names occurs multiple times in Col B.

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