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.

Dept	Name	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Total
Carp	Bob	4	8	8	8	4	OFF	OFF	32
Elec	Joe	4	4	4	4	8	OFF	OFF	24
AV	Sam	4	4	4	8	4	OFF	OFF	32
Audio	Ray	OFF	OFF	4	4	4	8	4	24

Carps Total Hrs
4 hrs
8hrs
Elec Total Hrs
4hrs
8hrs
AV Total Hrs
4hrs
8hrs
Elec Total Hrs
4hrs
8hrs

[tr]

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
1	Dept	Name	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Total	4	8	OFF
2	Carp	Bob	4	8	8	8	4	OFF	OFF	32	2	3	2
3	Elec	Joe	4	4	4	4	8	OFF	OFF	24	4	1	2
4	AV	Sam	4	4	4	8	4	OFF	OFF	32	4	1	2
5	Audio	Ray	OFF	OFF	4	4	4	8	4	24	4	1	2

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K2 =COUNTIF($C2:$I2,K$1) <tbody> </tbody>

<tbody>
</tbody>

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)