Totalling the number of instances a time is between a set of time ranges

[CyrusTexas]

Hello:

I have a question about adding up the number of employees on duty (column F - #staff ; see below) at particular times (column E - time) based on start and end times of staff shifts in column B and C, respectively. The data in columns B, C, and E are in the 6:00:00 AM format

For instance, at 6:00A there are two folks at work (Brooke and Charlie only who work from 6A to 12 noon so I would like to 6A time to total 2 and likewise for 6:30a, 7:00a, AMD 7:30a) but at 8:00A there are four staff on duty (Brooke, Charlie, Charlotte, and Chloe). Likewise, At 14:00 only Carla and Chloe are at work, and so on.

I have tried sumifs, countifs, and a whole range of other expressions but have not had much success in getting formulae in column F to work based on the time range information in columns B and C and I do not want to use pivot tables.

I hope I have made my question clear and can someone please help? Thank you!


A B C D E F
Name Start End Time #staff
Brooke 6:00 12:00 6:00 2
Carla 14:00 16:00 6:30 2
Charlie 6:00 10:30 7:00 2
Charlotte 8:00 12:30 7:30 2
Chloe 8:00 14:30 8:00 4

 

[jarjarbingie]

hello:

I have a question about adding up the number of employees on duty (column f - #staff ; see below) at particular times (column e - time) based on start and end times of staff shifts in column b and c, respectively. The data in columns b, c, and e are in the 6:00:00 am format

for instance, at 6:00a there are two folks at work (brooke and charlie only who work from 6a to 12 noon so i would like to 6a time to total 2 and likewise for 6:30a, 7:00a, amd 7:30a) but at 8:00a there are four staff on duty (brooke, charlie, charlotte, and chloe). Likewise, at 14:00 only carla and chloe are at work, and so on.

I have tried sumifs, countifs, and a whole range of other expressions but have not had much success in getting formulae in column f to work based on the time range information in columns b and c and i do not want to use pivot tables.

I hope i have made my question clear and can someone please help? Thank you!


A b c d e f
name start end time #staff
brooke 6:00 12:00 6:00 2
carla 14:00 16:00 6:30 2
charlie 6:00 10:30 7:00 2
charlotte 8:00 12:30 7:30 2
chloe 8:00 14:30 8:00 4

e2=countifs($b$2:$b$6,"<="&$d2,$c$2:$c$6,">="&$d2)

 

[CyrusTexas]

e2=countifs($b$2:$b$6,"<="&$d2,$c$2:$c$6,">="&$d2)

Thank you for your help in this matter but, unfortunately, I tried countifs before posting here and, although I appreciate your efforts for me, I pasted your formula into my Excel file but I do not get a result when I refer to the appropriate cells.

 

[jarjarbingie]

Thank you for your help in this matter but, unfortunately, I tried countifs before posting here and, although I appreciate your efforts for me, I pasted your formula into my Excel file but I do not get a result when I refer to the appropriate cells.

I used it with the sample data you have provided above and it works.. might be because of the data type of the time stamps..