Complex subtotal count and multiply

[Lightkeepr]

I have a table that lists shift types and a corresponding hourly value for it.

Division Manpower Board 1.0.xlsm
	D	E
1	Shift Code	Hours
2	R	40
3	O	50
4	H	32
5	O1	60
6	O2	70
7	H1	21
8	H2	16
DATA

On another worksheet I have a table of employees and in F2 I want it to subtotal how many times each value from TBL_ShiftCodes[Shift Code] appears in TBL_EMP_DATA[Column1] and then multiply that code by the hours for it. So in F2 it would show 80 for TBL_EMP_DATA[Column2], G2 would show 90 for TBL_EMP_DATA[Column3], H2 would show 72 for TBL_EMP_DATA[Column4], I2 would show 80 for TBL_EMP_DATA[Column5] and J2 would show 80for TBL_EMP_DATA[Column5].

The reason for using subtotal is that if any of the filtering on TBL_EMP_DATA for Name, Class, Crew,or Project is used, I want the counted values to change according to what is visible.

Division Manpower Board 1.0.xlsm
	A	B	C	D		F	G	H	I	J	K
1	Projected Man Hours Total for Bodies Shown: 0			Week Ending		04/04/2021	04/11/2021	04/18/2021	04/25/2021	05/02/2021	DO NOT USE THIS COLUMN FOR MANPOWER
2				Total Man Hours per Week
3	(if you hide/unhide week columns, click refresh above to update)			Available Hours per Week
4				Total Bodies per Week		2	2	2	2	2	0
5				School		1	0	0	0	0	0
6				Vacation		0	0	0	0	0	0
7
8	Name	Class	Crew	Project		Column1	Column2	Column3	Column4	Column5	END
9	Joe	F	1	161904		R	O	H	R	R
10	Karen	JW	1	161904		S
11	Chad	JW	1	150000		R	R	R	R	R
Manpower

Cell Formulas
Range	Formula
A1	A1	=CONCATENATE("Projected Man Hours Total for Bodies Shown: ",TEXT(sumVisible(F2:K2),"#,##0"))
F4	F4	=(SUBTOTAL(103,TBL_EMP_DATA[Column1])-SUBTOTAL(102,TBL_EMP_DATA[Column1]))-(F5+F6)
G4	G4	=(SUBTOTAL(103,TBL_EMP_DATA[Column2])-SUBTOTAL(102,TBL_EMP_DATA[Column2]))-(G5+G6)
H4	H4	=(SUBTOTAL(103,TBL_EMP_DATA[Column3])-SUBTOTAL(102,TBL_EMP_DATA[Column3]))-(H5+H6)
I4	I4	=(SUBTOTAL(103,TBL_EMP_DATA[Column4])-SUBTOTAL(102,TBL_EMP_DATA[Column4]))-(I5+I6)
J4	J4	=(SUBTOTAL(103,TBL_EMP_DATA[Column5])-SUBTOTAL(102,TBL_EMP_DATA[Column5]))-(J5+J6)
K4	K4	=(SUBTOTAL(103,TBL_EMP_DATA[END])-SUBTOTAL(102,TBL_EMP_DATA[END]))-(K5+K6)
F5	F5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="S")+0)
G5	G5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="S")+0)
H5	H5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="S")+0)
I5	I5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="S")+0)
J5	J5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="S")+0)
K5	K5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[END])-ROW(K9),0)),(TBL_EMP_DATA[END]="S")+0)
F6	F6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="V")+0)
G6	G6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="V")+0)
H6	H6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="V")+0)
I6	I6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="V")+0)
J6	J6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="V")+0)
K6	K6	=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[END])-ROW(K9),0)),(TBL_EMP_DATA[END]="V")+0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:K6	Expression	=F$6>0	text	NO
F9:K11	Expression	=F9="V"	text	NO
F5:K5	Expression	=F$5>0	text	NO
F9:K11	Expression	=F9="S"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B9:B11	List	=VAR_Class
C9:C11	List	=VAR_CrewID
D9:D11	List	=VAR_JobNumbers

 

[jasonb75]

Try this formula in F2, then drag right to fill the table.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(TBL_EMP_DATA[[#Headers],[Column1]],ROW(TBL_EMP_DATA[Column1])-ROW(TBL_EMP_DATA[[#Headers],[Column1]]),0,1,1))*(TBL_EMP_DATA[Column1]=TRANSPOSE(TBL_ShiftCodes[[Shift Code]:[Shift Code]]))*TRANSPOSE(TBL_ShiftCodes[[Hours]:[Hours]]))

 

[Lightkeepr]

Apparently I need to research more about the transpose function! This is amazing!