Rolling Hours sumed in a day

[namluke]

I tried to explain this in another post but messed the wording and uploaded the wrong file!

I hope the below makes more sense.

Cell A1 – H14 is the team rota, the time in each cell is the time they start their shift i.e. B8 Staff 6 starts at 12:30
Cells B16 C21 is the start time and the end time of the shift i.e. if staff 6 starts at 12:30 they finish at 21:00.

In Columns K – S I need this time frame to be counted within each 15 minute interval.

So in the example in Column M two members of staff start at 9:30 (Staff 1 & Staff7) so in column M from 9:30 till 12:30 it counts as 2 members of staff in, but at 12:30 when staff 6 & Staff 12 arrive the number increases to in column M row 26 to 4 members in. This continues to increase as more staff arrives throughout the day, until the staff members shifts come to an end, so when the 9:30 starters (Staff 1 & Staff7) leave at 18:00 the count drops to 4 members in.

Im looking for this to be automated so it can count the staffing levels for each day, so in column N it would reference the shift times in C3 : C14.

I hope that makes more sense, I apologies that the break down was so bad, I was in my own head on this one.

[Pauleyb]

Put this in M3 and drag across then down:
=COUNTIFS(B$3:B$14,"<="&$K29+1/1440,B$3:B$14,">="&$K29-0.354166666666667)

I had to add the 1/1440 due to the rounding of minutes (e.g. 1:30 in cell B7 was actually 0.5625, but in K29 it was 0.5624999999998.

I also think you had some improper values, such as at 1pm.

[Pauleyb]

Posted my answer, then noticed you have Excel 2003 which does not have COUNTIFS. Use this instead:
=SUMPRODUCT(--(B$3:B$14<=$K3+1/1440),--(B$3:B$14>=$K3-0.354166666666667))
But enter it with Cntl-Shift-Enter

[namluke]

Thank you so much!

This has made my spread sheet a lot cleaner!

Can I ask what is the logic behind +1/1440

Once again thank you for the help!

[Pauleyb]

See my initial post on why the 1/1440 was needed. If that still doesn't make sense, let me know.