counting dates in multiple ranges

Searshc

New Member
Joined
May 9, 2013
Messages
21
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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

New Member
Joined
May 9, 2013
Messages
21
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 DateEnd DateName
201509201517Smith
201511201519Joe
201509201517StanFiscal WeekCount
201511201519Lee2015084
201506201514Robert2015095
201506201514James201510Etc
201507201515Chris201511Etc
201501201509Leslie
201450201506Zack
201445201501John
201447201503Steve
201446201502Ryan
201436201444Mike
201438201446Bill
201434201442Henry
201433201441Kate
201431201439Jenny
201431201439Doc Oc
201428201436Frank

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

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,408
Messages
5,595,961
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top