Count based on multiple criteria
 

Hi Birdey,

Try ...

=SUM((A2:A1000=--LEFT(J2;8))*(A2:A1000<=--RIGHT(J2;8))*--(B2:B1000=L2))

This formula has to be entered with <ctrl<shift<enter

I've tried inonly one worksheet/workbook.

A2:A1000 is the date range in Chart Details, B2:B1000 is the technician in
the same sheet.

J2 in my formula is de week range in Chart Data , --left returns the first
date of the week while right gives the last date.

Probably you have to change 8 in left() & right() by find " -" to have the
exact length of the date in de cell.

Please let us know if this was of any help.

Wkr,

JP


"birdey" wrote in message
...
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