COUNT Function

[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?

 

[Guest]

=SUMPRODUCT(--(A1:A1),--(D110="6 AM")) will do that. For an explanation of
the -- symbol see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave

 

[Guest]

sumproduct(--($B$1:$B$100<TimeValue("6:00
AM")),--($C$1:$C$100>TimeValue("7:00 AM")))

would be a start.

 

[vezerid]

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?

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