Complex subtotal count and multiply

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
13
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,914
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
13
Office Version
  1. 365
Platform
  1. Windows
Apparently I need to research more about the transpose function! This is amazing!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,845
Members
418,155
Latest member
demasisi

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