Count based on multiple criteria

[birdey]

I have multiple sheets. One sheet identifies the date a ticket was closed
and the ID of the technician that closed the ticket.

Open Dt Closed Dt ID
04/22/09 06/05/09 JM44
04/17/09 06/05/09 X72Z
04/17/09 06/05/09 X72Z
05/21/09 06/05/09 X72Z
05/22/09 06/05/09 X86D
04/28/09 06/08/09 CG52
01/12/09 06/08/09 HD54
05/07/09 06/08/09 TBD
06/05/09 06/08/09 TBD
04/17/09 06/10/09 HD54

The second worksheet contains each week of the month/year. I need a
formula that calculates the number of tickets that were closed by tech id
based on the closure date and a week range.

Week # Clsd CG52 # Clsd JM44 # Clsd X72Z
06/01/2009 - 06/07/09 1 3
06/08/2009 - 06/14/09 1


Thanks in advance to anyone that can help me with this.

 

[Luke M]

Assuming your week range is actually in two cells (A2 & B2). Also you headers
witht he names of technicians need to have just the name of technician (same
as it appears in sheet1. Formula in C2 is:

=SUMPRODUCT(--(Sheet1!$B$2:$B$100>=$A2),--(Sheet1!$B$2:$B$100<=$B2),--(Sheet1!$C$2:$C$100=C$1))

 

[Jacob Skaria]

Suppose you have the data OpenDt,ClosedDt,ID in Sheet2 of your workbook.

In Sheet1 you have your data arranged as below..In sheet1 you need to have
the start date and end date in two columns and not in one column (separated
by -) as shown in your example..Also the dates should be in excel date format.

ColA ColB ColC ColD ColE
Start Date End Date CG52 JM44 X72Z
1/12/2009 6/10/2009 = = =

In cell C2 apply the below formula and copy that to right ....
=SUMPRODUCT(--(Sheet2!$B$1:$B$100>=$A2),--(Sheet2!$B$1:$B$100<=$B2),--(Sheet2!$C$1:$C$100=C$1))

If this post helps click Yes