Countifs or Sumifs

[Bound4Glory]

Hello,
I am wanting to count the number of employees that were on the clock between two times. I'm not sure how to go about it. For example:

EMPLOYEE NAME	START TIME	END TIME
BOB	3:00	7:00
BILLY	5:00	9:00
CRAIG	4:00	8:00
DAN	5:00	9:00

I would want to know the formula to return this below:

TIME	NUMBER OF EMPLOYEES ON CLOCK BETWEEN THESE TIMES
03:00 - 04:00	1
04:00 - 05:00	2
05:00 - 06:00	4
06:00 - 07:00	4
07:00 - 08:00	3
08:00 - 09:00	2

Thank you!

 

[jasonb75]

Firstly you would need to separate three times in the second table into 2 columns (start and end) having both in the same cell as a time range means that a formula can not read it properly as a time. Whilst there are ways to work around it, it would be better to get into the habit of using better methods sooner rather than later.

Assuming top table on columns A:C and bottom table in E:G (with 2 columns for times), the formula would be

=COUNTIFS(B:B,">="&E1,C:C,"<"&F1)