Count People within Date Ranges

[gahooja]

Hello, I have a set of data as described below

Team Resource Start Date End Date
PMO John Doe 2/1/10 7/24/10
Infra Jane Doe 3/1/10 4/23/10
ETC...

I need to be able to 1st match the team and see how many resources are active for each week for each team in a results table as shown below, i only used 2 teams and 2 resources for the example but i have a group of over 500 people so i was looking for a formula that might be able to 1st match the team and then count how many people are active within their date ranges shown above to the week end dates mentioned below.
Result
Team Wk Ending ..........Wk Ending........Wk Ending
2/6/10............3/6/10..............7/31/10
PMO 1...................1.....................0
Infra 0...................1.....................0

Thanks for the help

 

[schielrn]

Welcome to the Board!

The formulas in B3:D4 are if you have Excel 2007 and the formulas in B8:D9 can be used in pre-2007:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Team	Wk Ending	Wk Ending	Wk Ending			Team	Resource	Start Date	End Date
2		2/6/2010	3/6/2010	7/31/2010			PMO	John Doe	2/1/2010	7/24/2010
3	PMO	1	1	0			Infra	Jane Doe	3/1/2010	4/23/2010
4	Infra	0	1	0
5
6	Team	Wk Ending	Wk Ending	Wk Ending
7		2/6/2010	3/6/2010	7/31/2010
8	PMO	1	1	0
9	Infra	0	1	0
Sheet16

Cell Formulas
Range	Formula
B3	=COUNTIFS($G$2:$G$3,$A3,$I$2:$I$3,"<="&B$2,$J$2:$J$3,">="&B$2)
B8	=SUMPRODUCT(--($G$2:$G$3=$A3),--($I$2:$I$3<=B$2),--($J$2:$J$3>=B$2))

Hope that helps.

 

[gahooja]

Thanks that works. I was leaving out the & before the date cell so it wasn't picking it up.