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?