Count based on multiple criteria

[birdey]

I have a workbook that has multiple worksheets. On one worksheet ('Chart
Details') I have the following. Column A represents the date that a ticket
was closed and Column B indicates that userid of the technician that closed
the ticket. I need a formula that will calcluate the number of closed
tickets by technician by week. On the other worksheet ('Chart Data') I have
to reflect how many tickets each technician closed per week (e.g., 06/01/09 -
06/07/09, 06/08/09 - 06/14/09, etc.)

A B
2 06/01/09 CG52
3 06/02/09 CG52
4 06/05/09 X72Z
5 06/06/09 HD54
6 06/08/09 CG52
7 06/10/09 X72Z
8 06/11/09 CG52
9 06/11/09 X72Z

So, my formula I need to reflect the following


Week UserID # of Closed
tickets
06/01/09 - 06/07/09 CG52 2
06/01/09 - 06/07/09 X72Z 1
06/01/09 - 06/07/09 HD54 1
06/08/09 - -6/14/09 X72Z 2
06/08/09 - -6/14/09 CG52 2

 

[Luke M]

For your table, lets assume beginning of week is D2, end of week is E2,
UserID is F2. Formula in G2:
=SUMPRODUCT(--($A$2:$A$10>=D2),--($A$2:$A$10<=E2),--($B$2:$B$10=F2))

Note that you can't callout entire columns (A:A) in SUMPRODUCT unless using
XL 2007.

 

[birdey]

Let me clarify further. I have one worksheet ('Chart Details) that contains
the following:

Col G Col I
Dt. ID
Closed

06/05/09 X72Z
06/05/09 X72Z
06/05/09 X86D
06/08/09 CG52
06/08/09 HD54
06/10/09 HD54
06/12/09 EN19
06/17/09 X86D
06/17/09 X86D
06/18/09 X72Z

I have another worksheet 'Chart Data' where I need the formulas that will
tell me for each week, how many tickets were closed by each tech id. It's
the # Closed columns that I need the formula for. Thanks in advance!

Week Week # Closed # of Closed # of Closed
Beg Dt End Dt CG52 X72Z
HD54

06/01/09 06/07/09
06/08/09 06/14/09
06/15/09 06/21/09
06/22/09 06/28/09
06/29/09 07/02/09
07/03/09 07/09/09
07/10/09 07/16/09
07/17/09 07/23/09
07/24/09 07/30/09
07/31/09 08/02/09
08/03/09 08/09/09