This one has me stumped and from the few posts I've seen, the answer (add another field in source data) doesn;t work for me.
I have a staffing worksheet which shows total hours by month by job class and discipline (months on top, classes, disciplines down side). Hours per month also need to be shown in units or people per month. Each class can be in the field (200 hours = 1 unit) or in-house (174 hours = 1 unit). See table below.
Disicpline...Class...In-house/Field...May...June...July...August...Septemeber
Mech........ENG1........F...............200.....200...100......50..........100
Mech........ENG1........H................87.......87....174.....174.........174
Mech........DES1........H...............174.....174....174
Mech........PM1..........F..........................................200..........200
Now I have a SUMPRODUCT formula above each month to show total people per month, taking into account if they're marked as H or F...
=SUMPRODUCT(--($D$13:$D32="F"),F13:F32)/$F$6+SUMPRODUCT(--($D$13:$D32="H"),F13:F32)/$E$6
(F6 and E6 being where 200 and 174 are housed, respectively)
This formula row at the top allows me to produce a basic bar chart of people per month over the duration of a project. Yay. But I want to take it a step further. In a pivot table I would be able to sub-divide each month by class (and/or by discipline) being used in that month. If I only had one value representing a person/unit (say 200), I could simply create a calculated field of =Month/200. But the Field/In-House bit throws a wrench into that.
Is it possible to somehow create a SUMPRODUCT-type formula as a calculated field in a pivot table? Please note that there are many projects spanning several years and adding a column for each month representing a unit value is not an option.
I have a staffing worksheet which shows total hours by month by job class and discipline (months on top, classes, disciplines down side). Hours per month also need to be shown in units or people per month. Each class can be in the field (200 hours = 1 unit) or in-house (174 hours = 1 unit). See table below.
Disicpline...Class...In-house/Field...May...June...July...August...Septemeber
Mech........ENG1........F...............200.....200...100......50..........100
Mech........ENG1........H................87.......87....174.....174.........174
Mech........DES1........H...............174.....174....174
Mech........PM1..........F..........................................200..........200
Now I have a SUMPRODUCT formula above each month to show total people per month, taking into account if they're marked as H or F...
=SUMPRODUCT(--($D$13:$D32="F"),F13:F32)/$F$6+SUMPRODUCT(--($D$13:$D32="H"),F13:F32)/$E$6
(F6 and E6 being where 200 and 174 are housed, respectively)
This formula row at the top allows me to produce a basic bar chart of people per month over the duration of a project. Yay. But I want to take it a step further. In a pivot table I would be able to sub-divide each month by class (and/or by discipline) being used in that month. If I only had one value representing a person/unit (say 200), I could simply create a calculated field of =Month/200. But the Field/In-House bit throws a wrench into that.
Is it possible to somehow create a SUMPRODUCT-type formula as a calculated field in a pivot table? Please note that there are many projects spanning several years and adding a column for each month representing a unit value is not an option.
But being the Excel nerd that I am (I'd say 3rd degree to your 7th degree :P), I'll probably start looking into it now.
