COUNT Function

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I am working on a work schedule for a group of employees. They are schedule
to work through a range of time. For example, Mr. B works from 8 am to 5
PM, Mrs. C works from 10 am through 5 pm...ect. The layout would be similar
to the one below
A B C D F
Name start end Hour # of workers
Mr B 8 am 5 pm 6 am ?
Mrs C 10 am 5 pm 7 am ?
8 am ?

I want to know the number of workers scheduled for 6 am or from 6 am to 7
am, or each hour according to the range of start and end times to work on
the left side. How to I write a function that would accomplish this on
column F? I know I must use the count function, but I can' t put the logics
into the function. Can anybody help?
 
sumproduct(--($B$1:$B$100<TimeValue("6:00
AM")),--($C$1:$C$100>TimeValue("7:00 AM")))

would be a start.
 
I am working on a work schedule for a group of employees. They are schedule
to work through a range of time. For example, Mr. B works from 8 am to 5
PM, Mrs. C works from 10 am through 5 pm...ect. The layout would be similar
to the one below
A B C D F
Name start end Hour # of workers
Mr B 8 am 5 pm 6 am ?
Mrs C 10 am 5 pm 7 am ?
8 am ?

I want to know the number of workers scheduled for 6 am or from 6 am to 7
am, or each hour according to the range of start and end times to work on
the left side. How to I write a function that would accomplish this on
column F? I know I must use the count function, but I can' t put the logics
into the function. Can anybody help?
Click to expand...

Actually COUNT will not get you far here. If D2 stands for the 6-7am
slot, then in F2:
=SUMPRODUCT(($B$2:$B$10<=D2)*($C$2:$C$10>=D2))

All this, provided that your cells in columns B:C contain actual times
and not text that is interpreted as time by the human.

HTH
Kostis Vezerides
 
Back
Top