Count workers hours: I think this is easy. I am just not seeing it!

[baoldben]

Hi there,

I created a workbook that I use as a schedule for all workers in my department. The department breaks out into 4 other sub departments. All employees are scheduled for either a 4 or 8 hour shift. I need to quantify the data in a number of ways, most of which I was able to write on my own, however I am having trouble with one specific quantification. I need to count how many 4 or 8 hour shifts in each sub department. i.e.


[table="width: 500, class: grid"]
[tr]
[td]Dept[/td]
[td]Name[/td]
[td]Mon[/td]
[td]Tue[/td]
[td]Wed[/td]
[td]Thur[/td]
[td]Fri[/td]
[td]Sat[/td]
[td]Sun[/td]
[td]Total[/td]
[/tr]
[tr]
[td]Carp[/td]
[td]Bob[/td]
[td]4[/td]
[td]8[/td]
[td]8[/td]
[td]8[/td]
[td]4[/td]
[td]OFF[/td]
[td]OFF[/td]
[td]32[/td]
[/tr]
[tr]
[td]Elec[/td]
[td]Joe[/td]
[td]4[/td]
[td]4[/td]
[td]4[/td]
[td]4[/td]
[td]8[/td]
[td]OFF[/td]
[td]OFF[/td]
[td]24[/td]
[/tr]
[tr]
[td]AV[/td]
[td]Sam[/td]
[td]4[/td]
[td]4[/td]
[td]4[/td]
[td]8[/td]
[td]4[/td]
[td]OFF[/td]
[td]OFF[/td]
[td]32[/td]
[/tr]
[tr]
[td]Audio[/td]
[td]Ray[/td]
[td]OFF[/td]
[td]OFF[/td]
[td]4[/td]
[td]4[/td]
[td]4[/td]
[td]8[/td]
[td]4[/td]
[td]24[/td]
[/tr]
[/table]


[table="width: 500, class: grid"]
[tr]
[td]Carps Total Hrs[/td]
[td][/td]
[/tr]
[tr]
[td]4 hrs[/td]
[td][/td]
[/tr]
[tr]
[td]8hrs[/td]
[td][/td]
[/tr]
[tr]
[td]Elec Total Hrs[/td]
[td][/td]
[/tr]
[tr]
[td]4hrs[/td]
[td][/td]
[/tr]
[tr]
[td]8hrs[/td]
[td][/td]
[/tr]
[tr]
[td]AV Total Hrs[/td]
[td][/td]
[/tr]
[tr]
[td]4hrs[/td]
[td][/td]
[/tr]
[tr]
[td]8hrs[/td]
[td][/td]
[/tr]
[tr]
[td]Elec Total Hrs[/td]
[td][/td]
[/tr]
[tr]
[td]4hrs[/td]
[td][/td]
[/tr]
[tr]
[td]8hrs[/td]
[td][/td]
[/table]

I was able to calculate the total hours of each department with Sumif. However I need to break out each department by 4 hrs and 8 hrs not with a total hour count but by the number of times they were scheduled for 4hrs i.e. Carp Bob was schedule for 2 4 hr shifts not 8 hours total. Does this make sense? I hope I am being clear. It seems like a simple formula that I can't seem to get my head around. Any help is appreciated.

 

[Steve_R]

I would change the layout as follows but the Countif() principal is the same if you wish to retain the layout you have (you'll just need to do more work creating formula).

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
Dept	Name	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Total	OFF
Carp	Bob	OFF	OFF
Elec	Joe	OFF	OFF
AV	Sam	OFF	OFF
Audio	Ray	OFF	OFF

<tbody>
[TD="align: center"]1[/TD]

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

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

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

[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

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

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

[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

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

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

[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=COUNTIF($C2:$I2,K$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Drag or copy K2 formula to required cells

Possibly, =COUNTIF($C2:$I2,K$1)*K$1 if you want separated tally of 4hr and 8hr shifts (the OFF is there as an example)