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(--(A1:A1),--(D1:D10="6 AM")) will do that. For an explanation of
the -- symbol see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Richard" wrote:

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$100TimeValue("7:00 AM")))

would be a start.

--
Regards,
Tom Ogilvy



"Richard" wrote:

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?

On Mar 12, 7:19 pm, "Richard" wrote:
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