Calculating Total Hours Worked Each Day

[Jim]

At the top of my sheet I have a work schedule for a month,
showing different shifts and the hours they work on any
given day, ex:
Shift 1 2 3
A 10 10 0
B 8 0 0
C 0 8 8

Below that I have a list of employee numbers, the shift
they are on, and a list of days in the month, ex:
Employee Shift 1 2 3
0001 A
0002 B
0003 C

At the end of each day (bottom of the column), I would
like to sum how many hours of work are scheduled for all
employees. For the simple example above, it would equal
18 for the 1st or 2nd, and 8 for the 3rd. How can I do
this? Thanks.

 

[Homey]

in the cell for the total hours for employee 1 type given that the
first value is in B2 and the last value is in B4.
=SUM(B2:B4)

and modify accordingly

=SUM(C2:C4)
=SUM(D24)

for the other 2 employees

 

[Jim]

I probably didn't do a good job of explaining this. I'll
try and clarify:
The employees are not 1,2,3, they are 0001,0002,0003...

1,2,3... are days of the month.

There are actually hundreds of employees, and probably
about 10 different shift schedules (A,B,C...).

I just want to search all of the employee's shift
schedules in the bottom list, and add the total hours
scheduled to work on any given day, based on the
information in the top list.

 

[Guest]

Does your second table mean that 0001 works shift A on the 1st? If Employee 0003 works shift C on the first then they don't work that day? Would an employee work on different shifts on different days?

I think you might be able to use a COUNTIF on your second table to add up the number of employees on any given shift. You could then multiply by the hours of that shift using INDEX and MATCH on the first table. Then SUM them all up to get the total hours worked for the day.

I have put together a sample spread sheet if you want me to e-mail it to you.

Good Luck,
Mark Graesser
(e-mail address removed)


----- Jim wrote: -----

I probably didn't do a good job of explaining this. I'll
try and clarify:
The employees are not 1,2,3, they are 0001,0002,0003...

1,2,3... are days of the month.

There are actually hundreds of employees, and probably
about 10 different shift schedules (A,B,C...).

I just want to search all of the employee's shift
schedules in the bottom list, and add the total hours
scheduled to work on any given day, based on the
information in the top list.

-----Original Message-----
in the cell for the total hours for employee 1 type given that the
first value is in B2 and the last value is in B4.
=SUM(B2:B4)

and modify accordingly
=SUM(C2:C4) =SUM(D24)
for the other 2 employees

 

[Jim]

Mark, an employee would work the same shift schedule
throughout the month. If 0001 is on A, he will always be
on A, but of course that means that some days he's
working, and some days he's off, based on shift schedule A.

I think that I can picture what you are saying about using
COUNTIF to determine how many people are following a
particular schedule, and then multiplying it by the hours
scheduled for a given day.

However, it is possible that at some point in time new
schedules will be created and some employee's will be
reassigned. I don't want to have to go back and change
all of the formulas to reflect the fact that there is
suddenly a D and E shift. I was hoping to find some kind
of array formula that could vlookup every employee's shift
and sum up the work hours on a given day.

-----Original Message-----
Does your second table mean that 0001 works shift A on

the 1st? If Employee 0003 works shift C on the first then
they don't work that day? Would an employee work on
different shifts on different days?

I think you might be able to use a COUNTIF on your second

table to add up the number of employees on any given
shift. You could then multiply by the hours of that shift
using INDEX and MATCH on the first table. Then SUM them
all up to get the total hours worked for the day.

I have put together a sample spread sheet if you want me to e-mail it to you.

Good Luck,
Mark Graesser
(e-mail address removed)


----- Jim wrote: -----

I probably didn't do a good job of explaining this. I'll
try and clarify:
The employees are not 1,2,3, they are 0001,0002,0003...

1,2,3... are days of the month.

There are actually hundreds of employees, and probably
about 10 different shift schedules (A,B,C...).

I just want to search all of the employee's shift
schedules in the bottom list, and add the total hours
scheduled to work on any given day, based on the
information in the top list.

-----Original Message-----
in the cell for the total hours for employee 1 type

given
that the

 

[Guest]

Jim
I think the file I put together might get you started in the right direction. Since your hours per shift is dependent on two variables, the shift letter AND the day of the month, a standard LOOKUP won't work. I have set up an INDEX function which matches the shift letter and the day of the month on your table, and retreives the intersecting cell

If you anticipate adding new shifts that can also be accomodated with this setup. As long as you insert rows WITHIN the existing table, not at the end, then the formulas which refer to the table will update automatically. You could do this by having a fictitious shift Z, then any new shifts will be inserted above it

If you would like to see the file just repost with your e-mail address, or you could e-mail me directly

Good Luck
Mark Graesse
(e-mail address removed)

----- Jim wrote: ----

Mark, an employee would work the same shift schedule
throughout the month. If 0001 is on A, he will always be
on A, but of course that means that some days he's
working, and some days he's off, based on shift schedule A

I think that I can picture what you are saying about using
COUNTIF to determine how many people are following a
particular schedule, and then multiplying it by the hours
scheduled for a given day

However, it is possible that at some point in time new
schedules will be created and some employee's will be
reassigned. I don't want to have to go back and change
all of the formulas to reflect the fact that there is
suddenly a D and E shift. I was hoping to find some kind
of array formula that could vlookup every employee's shift
and sum up the work hours on a given day

-----Original Message----
Does your second table mean that 0001 works shift A on

the 1st? If Employee 0003 works shift C on the first then
they don't work that day? Would an employee work on
different shifts on different daystable to add up the number of employees on any given
shift. You could then multiply by the hours of that shift
using INDEX and MATCH on the first table. Then SUM them
all up to get the total hours worked for the day