Count based on date criteria
 

I Want to create a formula that will tell me based on any given day how many
tickets were unresolved. I have the date fields "Call Taken" (Column B) and
"Closed" (Column E).

For instance, if on 12/9/09, there were 2 tickets opened on 12/08 and closed
on the same day, the formula should say 0. If there were 2 tickets opened on
12/03 and one of them was closed on 12/05, then 1 remains open on 12/09 and
the formula should show that. As well, if there was a ticket opened on 12/07
that was closed on 12/11, it should show as open in the formula because on
12/9 it was still open. Of course, if there was a ticket opened on 12/12
that remains open and one opened on 12/13 that was closed on 12/14, neither
should be reported by the formula since they occurred after the target date
of 12/9/09

So far, I'm performing a simple subtract of "Closed" tickets
(SUM(65536-COUNTBLANK(E:E))) from count of "Call Taken" (COUNTA(B:B)) which
only works for current reports. If I want to do a historical report based on
a prior date, I get the current number of tickets still open


Note: The report is run in the morning before tickets are worked on so if a
ticket is opened on the date the report is run, it won't show up on the
report yet.
Based on the sample table below, reports pulled on the following dates
should yield these results:

12/02/2009 - 1 (120901)
12/03/2009 - 1 (120901)
12/04/2009 - 3 (120901, 120908, 120909)


ID Call Taken Due Date Work Started Closed Unit
120901 12/01/2009 12/07/2009 C
120902 12/01/2009 12/01/2009 12/01/2009 D
120903 12/01/2009 12/01/2009 12/01/2009 D
120904 12/02/2009 12/02/2009 12/02/2009 H
120905 12/02/2009 12/02/2009 12/02/2009 C
120906 12/02/2009 12/02/2009 12/02/2009 B
120907 12/03/2009 12/03/2009 12/03/2009 H
120908 12/03/2009 12/03/2009 12/04/2009 F
120909 12/03/2009 12/03/2009 D
120910 12/04/2009 12/07/2009 12/07/2009 C
120911 12/04/2009 12/04/2009 12/07/2009 C
120912 12/08/2009 12/08/2009 12/08/2009 H
120913 12/08/2009 12/08/2009 12/09/2009 H
120914 12/09/2009 12/09/2009 12/14/2009 F
120915 12/09/2009 12/09/2009 12/14/2009 C
120916 12/09/2009 12/09/2009 12/09/2009 D
120917 12/10/2009 12/10/2009 F
120918 12/10/2009 12/10/2009 12/11/2009 H
120919 12/11/2009 12/11/2009 12/14/2009 B
120920 12/11/2009 12/14/2009 A

Not sure how that table is gonna look once I send this (there are 6 columns)

Hope I haven't described this in a confusing manner

Count based on date criteria
 

Erich,

With 12/09 in cell A1, a formula like

=SUMPRODUCT((B2:B1000<=A1)*(C2:C1000=A1)

will return the count of open tickets. You will need to adjust the <= (try < only) and the = (try
only) depending on what you want to do if the date is exact (your examples don't include a value
of 12/09).

HTH,
Bernie
MS Excel MVP


"Erich" wrote in message
...
I Want to create a formula that will tell me based on any given day how many
tickets were unresolved. I have the date fields "Call Taken" (Column B) and
"Closed" (Column E).

For instance, if on 12/9/09, there were 2 tickets opened on 12/08 and closed
on the same day, the formula should say 0. If there were 2 tickets opened on
12/03 and one of them was closed on 12/05, then 1 remains open on 12/09 and
the formula should show that. As well, if there was a ticket opened on 12/07
that was closed on 12/11, it should show as open in the formula because on
12/9 it was still open. Of course, if there was a ticket opened on 12/12
that remains open and one opened on 12/13 that was closed on 12/14, neither
should be reported by the formula since they occurred after the target date
of 12/9/09

So far, I'm performing a simple subtract of "Closed" tickets
(SUM(65536-COUNTBLANK(E:E))) from count of "Call Taken" (COUNTA(B:B)) which
only works for current reports. If I want to do a historical report based on
a prior date, I get the current number of tickets still open


Note: The report is run in the morning before tickets are worked on so if a
ticket is opened on the date the report is run, it won't show up on the
report yet.
Based on the sample table below, reports pulled on the following dates
should yield these results:

12/02/2009 - 1 (120901)
12/03/2009 - 1 (120901)
12/04/2009 - 3 (120901, 120908, 120909)


ID Call Taken Due Date Work Started Closed Unit
120901 12/01/2009 12/07/2009 C
120902 12/01/2009 12/01/2009 12/01/2009 D
120903 12/01/2009 12/01/2009 12/01/2009 D
120904 12/02/2009 12/02/2009 12/02/2009 H
120905 12/02/2009 12/02/2009 12/02/2009 C
120906 12/02/2009 12/02/2009 12/02/2009 B
120907 12/03/2009 12/03/2009 12/03/2009 H
120908 12/03/2009 12/03/2009 12/04/2009 F
120909 12/03/2009 12/03/2009 D
120910 12/04/2009 12/07/2009 12/07/2009 C
120911 12/04/2009 12/04/2009 12/07/2009 C
120912 12/08/2009 12/08/2009 12/08/2009 H
120913 12/08/2009 12/08/2009 12/09/2009 H
120914 12/09/2009 12/09/2009 12/14/2009 F
120915 12/09/2009 12/09/2009 12/14/2009 C
120916 12/09/2009 12/09/2009 12/09/2009 D
120917 12/10/2009 12/10/2009 F
120918 12/10/2009 12/10/2009 12/11/2009 H
120919 12/11/2009 12/11/2009 12/14/2009 B
120920 12/11/2009 12/14/2009 A

Not sure how that table is gonna look once I send this (there are 6 columns)

Hope I haven't described this in a confusing manner