Counting how often a time appears

[SilentDisco]

Hi,

I am hoping someone out there will be able to point me in the right direction as I have been searching for ages and can not seem to get anything to work that is not really long winded.

A B C D E

	Start	Lunch start	Lunch Finish	Finish
Staff A	09:30	13:30	14:30	18:00
Staff B	11:00	15:00	16:00	19:00
Staff C	09:00	13:00	14:00	17:00
Staff D	09:30	13:30	14:30	18:00
Staff E	09:00	13:00	14:00	17:00
Staff F	10:00	14:00	15:00	19:00
Staff G	09:00	13:00	14:00	17:00

<tbody>
</tbody>

I have a table that shows a staff members shift pattern on any given day (this goes on for around 100 rows)

I need to count how many people are working at any given 15 minute interval so have to remove the hour they have for lunch

09:00	2
09:15	2
09:30	4
09:45	4
10:00	5
10:15	5
10:30	5
10:45	5
11:00	6
11:15
11:30
11:45
12:00
12:15

<tbody>
</tbody>

I hope I have explained this well enough, any help will be greatly appreciated.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUMPRODUCT(($B$2:$B$8<=A12)*($C$2:$C$8>=A12)+($D$2:$D$8<=A12)*($E$2:$E$8>=A12))
or
=COUNTIFS($B$2:$B$8,"<="&A12,$C$2:$C$8,">="&A12)+COUNTIFS($D$2:$D$8,"<="&A12,$E$2:$E$8,">="&A12)

 

[SilentDisco]

Thanks that works perfectly, I do not know why I could not get it to work for me

Much appreciated

 

[Fluff]

You're welcome & thanks for the feedback