counting dates in multiple ranges

[Searshc]

I have a sheet with multiple employees training dates setup as a numbered range (ie, fiscal year/fiscal week like 201604). Because the training is not always the same length of time, I need to be able to show how many employees are in training for each fiscal week. However, because the data is stored per employee, I have multiple training start/end dates, and every time I try using 'countifs' it comes back with zero, even though I know I have 79 people in training this week.

Is there a way to countif for each employee since the date ranges are there for each person?

 

[Joyner]

Hello, can you post a sample of your data and the expected result? Otherwise, with the limited information you have provided, it would be very difficult to give you a solution.

 

[Searshc]

Yes, I hope this helps (not sure how to format this). Basically I have a start and end date, by fiscal week with the employee names. In another column, I have a list of the fiscal dates and I need a count for the number of employees in training within that time frame (the ones in A and B). Countifs kept coming up with zero. I can look up what date they start, but it won't keep track of the number of weeks they are actively in training, and then I can count the end date. But I need to show week by week how many people are in training.

Start Date	End Date	Name
201509	201517	Smith
201511	201519	Joe
201509	201517	Stan		Fiscal Week	Count
201511	201519	Lee		201508	4
201506	201514	Robert		201509	5
201506	201514	James		201510	Etc
201507	201515	Chris		201511	Etc
201501	201509	Leslie
201450	201506	Zack
201445	201501	John
201447	201503	Steve
201446	201502	Ryan
201436	201444	Mike
201438	201446	Bill
201434	201442	Henry
201433	201441	Kate
201431	201439	Jenny
201431	201439	Doc Oc
201428	201436	Frank

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Joyner]

Hello try this - assumes data with headings is at A1 to C20, and criteria with headings starts at D1, formula starts at E2 for 201508 at D2:

=COUNTIFS($A$2:$A$20,"<="&D2,$B$2:$B$20,">="&D2)