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[TABLE="width: 476"]
<tbody>[TR]
[TD][/TD]
[TD]Start[/TD]
[TD]Lunch start[/TD]
[TD]Lunch Finish[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]Staff A[/TD]
[TD]09:30[/TD]
[TD]13:30[/TD]
[TD]14:30[/TD]
[TD]18:00[/TD]
[/TR]
[TR]
[TD]Staff B[/TD]
[TD]11:00[/TD]
[TD]15:00[/TD]
[TD]16:00[/TD]
[TD]19:00[/TD]
[/TR]
[TR]
[TD]Staff C[/TD]
[TD]09:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]Staff D[/TD]
[TD]09:30[/TD]
[TD]13:30[/TD]
[TD]14:30[/TD]
[TD]18:00[/TD]
[/TR]
[TR]
[TD]Staff E[/TD]
[TD]09:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]Staff F[/TD]
[TD]10:00[/TD]
[TD]14:00[/TD]
[TD]15:00[/TD]
[TD]19:00[/TD]
[/TR]
[TR]
[TD]Staff G[/TD]
[TD]09:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]17:00[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]09:00[/TD]
[TD="width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]09:15[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]09:30[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]09:45[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]10:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]10:15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]10:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]10:45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]11:00[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]11:15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]11:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]11:45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]12:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]12:15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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