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

 

[JP Ronse]

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

 

[Ashish Mathur]

Hi,

You could use a pivot table. Drag date and user ID to the row area, drag
user id once again the data area (to get the count). Now click on any cell
in the date column and on the pivot table toolbar, go to Group and Show
detail > Group > Days > Type 7 in the box

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com