Complex subtotal count and multiply

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have a table that lists shift types and a corresponding hourly value for it.
Division Manpower Board 1.0.xlsm
DE
1Shift CodeHours
2R40
3O50
4H32
5O160
6O270
7H121
8H216
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
ABCDFGHIJK
1Projected Man Hours Total for Bodies Shown: 0Week Ending04/04/202104/11/202104/18/202104/25/202105/02/2021DO NOT USE THIS COLUMN FOR MANPOWER
2Total Man Hours per Week
3(if you hide/unhide week columns, click refresh above to update)Available Hours per Week
4Total Bodies per Week222220
5School100000
6Vacation000000
7
8NameClassCrewProjectColumn1Column2Column3Column4Column5END
9JoeF1161904ROHRR
10KarenJW1161904S
11ChadJW1150000RRRRR
Manpower
Cell Formulas
RangeFormula
A1A1=CONCATENATE("Projected Man Hours Total for Bodies Shown: ",TEXT(sumVisible(F2:K2),"#,##0"))
F4F4=(SUBTOTAL(103,TBL_EMP_DATA[Column1])-SUBTOTAL(102,TBL_EMP_DATA[Column1]))-(F5+F6)
G4G4=(SUBTOTAL(103,TBL_EMP_DATA[Column2])-SUBTOTAL(102,TBL_EMP_DATA[Column2]))-(G5+G6)
H4H4=(SUBTOTAL(103,TBL_EMP_DATA[Column3])-SUBTOTAL(102,TBL_EMP_DATA[Column3]))-(H5+H6)
I4I4=(SUBTOTAL(103,TBL_EMP_DATA[Column4])-SUBTOTAL(102,TBL_EMP_DATA[Column4]))-(I5+I6)
J4J4=(SUBTOTAL(103,TBL_EMP_DATA[Column5])-SUBTOTAL(102,TBL_EMP_DATA[Column5]))-(J5+J6)
K4K4=(SUBTOTAL(103,TBL_EMP_DATA[END])-SUBTOTAL(102,TBL_EMP_DATA[END]))-(K5+K6)
F5F5=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="S")+0)
G5G5=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="S")+0)
H5H5=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="S")+0)
I5I5=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="S")+0)
J5J5=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="S")+0)
K5K5=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[END])-ROW(K9),0)),(TBL_EMP_DATA[END]="S")+0)
F6F6=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="V")+0)
G6G6=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="V")+0)
H6H6=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="V")+0)
I6I6=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="V")+0)
J6J6=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="V")+0)
K6K6=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[END])-ROW(K9),0)),(TBL_EMP_DATA[END]="V")+0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F6:K6Expression=F$6>0textNO
F9:K11Expression=F9="V"textNO
F5:K5Expression=F$5>0textNO
F9:K11Expression=F9="S"textNO
Cells with Data Validation
CellAllowCriteria
B9:B11List=VAR_Class
C9:C11List=VAR_CrewID
D9:D11List=VAR_JobNumbers
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,883
Office Version
  1. 365
Platform
  1. Windows
Try this formula in F2, then drag right to fill the table.
Excel Formula:
=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]]))
 
Solution

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Apparently I need to research more about the transpose function! This is amazing!
 

Forum statistics

Threads
1,144,242
Messages
5,723,203
Members
422,483
Latest member
BijanBorazjani

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top