head count on rota

[Guest]

anyone know how i can get excel to count the number of people i have between
7-8 , 8-9, 9-10 etc. i have created the rota in excel all times are in HH:MM
format using seperate columns for start and finish time.
e.g.
in b out
06:00 1 15:00
06:00 0 13:00
06:00 1 14:00

06:00 1 15:00
09:00 1 15:00
07:00 1 16:00

 

[Biff]

Hi!

Try one of these.

=SUMPRODUCT(--(A2:A8>=TIME(7,0,0)),--(C2:C8>=TIME
(8,0,0)),B2:B8)

OR, enter your time ranges in cells:

E1 = 7:00 AM
F1 = 8:00 AM

=SUMPRODUCT(--(A2:A8>=E1),--(C2:C8>=F1),B2:B8)

Biff