Work schedule calculation

[tht78]

Hi! Hopefully someone can push in the right direction with my excel problem. Attached is a sample of my spreadsheet. It is a work schedule for several persons on several different work shifts, all with different lengths. My goal is to add a overview for each person with the total number of hours worked per week. I would prefer to do the calculation in one cell for each person because any use of helper colums or tables would increase dramatically when i expand number of people and number of shifts.
If the shifts were all the same length it would be no problem. I could just do a COUNTIF(C3:J7, person)*shiftlength. But when each shift has a unique length i will have to calculate each row one by one and then add them. I have not succeeded in finding a way to do this in a single cell calculation yet.
The calculation SUM({array_of_shifts}*{array_of shiftlengths}) CTRL-SHIFT-ENTER will produce the correct answer, but how do I get to the first array??

I want the COUNTIF to produce one answer for each row/shift, not a total for the whole range.

 

[Eric W]

Welcome to the Board!

Try this:

Excel 2012

	A	B	C	D	E	F	G	H	I
1		Week 1
2	Shift	Hrs/Day	mon	tue	wed	thu	fri	Sat	Sun
3	Morning	8.5	John	John	John	Lisa	Lisa
4	Evening	8	Lisa	Lisa	Gary	Gary	Gary
5	Night	7.5	Paul	Paul	Paul	Paul	John
6	Weekend Day	11.75						Lisa	Lisa
7	Weekend Night	12.25						John	John
8		Week 2
9
10	Shift	Hrs/Day	mon	tue	wed	thu	fri	Sat	Sun
11	Morning	8.5	Lisa	Lisa	Lisa	Paul	Paul
12	Evening	8	Paul	Paul	John	John	John
13	Night	7.5	Gary	Gary	Gary	Gary	Lisa
14	Weekend Day	11.75						Paul	Paul
15	Weekend Night	12.25						Lisa	Lisa
16
17		Hrs/weeks
18	Employee	Week 1	Week 2	Total
19	John	57.5	24	81.5
20	Lisa	56.5	57.5	114
21	Gary	24	30	54
22	Paul	30	56.5	86.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B19 =SUMPRODUCT(($C$3:$I$7=$A19)*$B$3:$B$7) C19 =SUMPRODUCT(($C$11:$I$15=$A19)*$B$11:$B$15) D19 =SUM(B19:C19) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[tht78]

Thank you Eric! That simple, huh? I was sure I had tried that formula but apparently not. I see it works with both SUM and SUMPRODUCT also but the latter is perhaps preferred?

 

[Eric W]

Yes, you can use SUM with Control+Shift+Enter. I have read that SUMPRODUCT is a bit more efficient than SUM, but the overall processing is the same. I doubt you'd notice any difference unless you had LOTS of formulas. SUMPRODUCT doesn't require CSE either, which is a plus for some people.

Glad to help!