Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Using Countif with dates

    We've created a workbook in Excel 2003 for the boss. We have a query to download our trouble tickets from SQL. We want to track the daily new tickets and closed tickets. We currently are using the COUNTIF function for each day to get the number of tickets Opened and another calculation for the number Closed. We then subtract these numbers for the day to get the trend.

    We started with the number of tickets open as of 1/1/2011. Daily we add/subtract the trend amount, which in theory should balance to our actual number of tickets open. Doesn't work. Currently we are off by 10.

    The current formula is COUNTIF(Query!E:E,a2). Count the Open date column if the date equals what is in cell A1. There is a similar formula for Closed date. The daily numbers produced by these calculations seem to balance with the database itself.

    One question is why would the formula to keep track of the current number of tickets not work

    Another question is what is the format of the COUNTIF function if we want to get the count of tickets opened before 1/1/2011? We've tried COUNTIF(Query!E:E, "<01/01/2011"). We even tried using the number that Excel uses for the date (40544). We either get 0 or the total number of tickets. Is this issue because the information is downloaded from SQL and therefore the empty fields (no closed date) are causing issues?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    COUNTIF(Query!E:E,a2) will find an exact match. If the value in the cell has a time component it will not match exactly. If the value is not a number it will not macch.

    COUNTIF(Query!E:E, "<01/01/2011") or COUNTIF(query!E:E, "<40544") should both work: they work for me in my tests...

    Could you post an example file with proprietary info removed that demonstrates the problem?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    When I use the COUNTIF(Query!E:E,"<40544"), I get the total number of records on the "2011" worksheet, not just the record less that the date.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    There are 2 issues. Your formula is:
    =COUNTIF(Query!E:E,"<K1")
    This literally looks for the values to be less than the literal string "K1". You want to look at less than what is in the cell K1 so you must use:
    =COUNTIF(E:E,"<"&K1)

    The second problem is that the cells in Query!E:E do NOT contain numbers, they contain text that looks like numbers. To convert them to numbers:
    select a blank cell
    edit -copy
    select the range of date-numbers (Cols D&E)
    Edit - Paste special - ADD [OK]

    They will be numbers, Format the cells as desired.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    The second problem is probably something we cannot change. We download the data daily, which would require that we convert the data to numbers after each download. We will test to see if it is worth the change.

    I don't know if this solves or explains the issue with the daily count being off by 10.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you can't change the 2nd problem I think you will have problems if you ever want to use countif especially with greater and less than. I am not sure it is possible to tell the countif to look for a literal string that looks like a number.

    I am not sure exactly what the problem is with daily counts, but it would not surprise me if it were related to this in some way. I am not sure what number is off by 10 in the workbook. Could you elaborate?

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Will these work ......

    For dates less than 2011-01-01 the answer is 887
    =COUNTIF(Query!E:E,"<2011-01-01""")

    For dates greater than or equal to 2011-01-01 the answer is 101
    =COUNTIF(Query!E:E,">=2011-01-01""")-1

    The total of the two formulas is 988.

  8. The Following User Says Thank You to tfspry For This Useful Post:

    bjg1 (2011-03-10)

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    tfspry method will NOT work correctly if the data set had the a cell with a date of 2011-01-01 in it. This value would be counted as less than the lookup, not equal to it as desired due to the extra character that is added.

    The formula works since adding the extra quotation mark (it does not have to be a quotation mark, any character will work the same way), it forces XL to consider the number text.

    To do this type of countif with K1:
    Change the formula in K1 to display the date as text in the correct format:
    =TEXT(A2,"yyyy-mm-dd")&Char(1)

    Then you can use:
    =COUNTIF(Query!E:E,"<="&K1)

    [tfspry does not mention the reason, but the "-1" in the 2nd formula is to not count the Header value from row 1. The text "RecvdDate" is > than the text phrase 2011-01-01" so is counted and must be removed from the total.]

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    bjg1 (2011-03-10)

  11. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by sdckapr View Post
    If you can't change the 2nd problem I think you will have problems if you ever want to use countif especially with greater and less than. I am not sure it is possible to tell the countif to look for a literal string that looks like a number.......
    My formula was offering an alternative that does give the same answer as Steve's new post.

    My formula eliminated the need for the previously proposed solution of ...
    "The second problem is that the cells in Query!E:E do NOT contain numbers, they contain text that looks like numbers. To convert them to numbers:
    select a blank cell
    edit -copy
    select the range of date-numbers (Cols D&E)
    Edit - Paste special - ADD [OK]
    They will be numbers, Format the cells as desired."

    My alternative was just offered to avoid the above steps. It also avoids the need to refer to cell K1 and creating a new formula in cell K1. It also gives the option to type in the date of your choosing within the COUNTIF formula.

  12. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts
    You can also use SUMPRODUCT:
    =SUMPRODUCT(--(Query!E2:E989+0<K1))

    Note that pre-2007 you cannot pass an entire column to SUMPRODUCT so you need to specify a range (though you can use a dynamic named range if required)
    Regards,
    Rory
    Microsoft MVP - Excel.

  13. The Following User Says Thank You to rory For This Useful Post:

    bjg1 (2011-03-10)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •