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
Week 1
Shift	Hrs/Day	mon	tue	wed	thu	fri	Sat	Sun
Morning	John	John	John	Lisa	Lisa
Evening	Lisa	Lisa	Gary	Gary	Gary
Night	Paul	Paul	Paul	Paul	John
Weekend Day	Lisa	Lisa
Weekend Night	John	John
Week 2
Shift	Hrs/Day	mon	tue	wed	thu	fri	Sat	Sun
Morning	Lisa	Lisa	Lisa	Paul	Paul
Evening	Paul	Paul	John	John	John
Night	Gary	Gary	Gary	Gary	Lisa
Weekend Day	Paul	Paul
Weekend Night	Lisa	Lisa
Hrs/weeks
Employee	Week 1	Week 2	Total
John
Lisa
Gary
Paul

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8.5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7.5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]11.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]12.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]8.5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]7.5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]11.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]12.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]57.5[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]81.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]56.5[/TD]
[TD="align: right"]57.5[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]56.5[/TD]
[TD="align: right"]86.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B19[/TH]
[TD="align: left"]=SUMPRODUCT(($C$3:$I$7=$A19)*$B$3:$B$7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]=SUMPRODUCT(($C$11:$I$15=$A19)*$B$11:$B$15)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]=SUM(B19:C19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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