Results 1 to 10 of 10
Thread: Using Countif with dates

20110309, 09:06 #1
 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?

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20110309, 10:04 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,165
 Thanks
 14
 Thanked 317 Times in 311 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

20110309, 12:38 #3
 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.

20110309, 13:44 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,165
 Thanks
 14
 Thanked 317 Times in 311 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 datenumbers (Cols D&E)
Edit  Paste special  ADD [OK]
They will be numbers, Format the cells as desired.
Steve

20110309, 14:44 #5
 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.

20110309, 16:12 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,165
 Thanks
 14
 Thanked 317 Times in 311 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

20110309, 17:53 #7
 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 20110101 the answer is 887
=COUNTIF(Query!E:E,"<20110101""")
For dates greater than or equal to 20110101 the answer is 101
=COUNTIF(Query!E:E,">=20110101""")1
The total of the two formulas is 988.

The Following User Says Thank You to tfspry For This Useful Post:
bjg1 (20110310)

20110309, 18:13 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,165
 Thanks
 14
 Thanked 317 Times in 311 Posts
tfspry method will NOT work correctly if the data set had the a cell with a date of 20110101 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,"yyyymmdd")&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 20110101" so is counted and must be removed from the total.]
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
bjg1 (20110310)

20110309, 21:19 #9
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
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 datenumbers (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.

20110310, 05:49 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 5,870
 Thanks
 0
 Thanked 78 Times in 74 Posts
You can also use SUMPRODUCT:
=SUMPRODUCT((Query!E2:E989+0<K1))
Note that pre2007 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.

The Following User Says Thank You to rory For This Useful Post:
bjg1 (20110310)