I have a number of summary tables that are split by various fields. For reporting, I need to produce the subtotals of every combination of field values. For example, consider the table below (all following tables can be found in the attached document):
RawTable.png
I would need to produce the following summaries:
SummaryTable.png
Using a single PivotTable, I can put Month into the column fields and Var1 and Var2 into the row fields which would produce most of the subtotals, but it would not produce, say, the split by Var2 only (X = 585, Y = 1170, Z = 2340). The PivotTable is below.
PivotTable.png
Is there a way to get the PivotTable to include all of the subtotal for all of the combinations of fields?
Note that I am specifically avoiding the SUMIF and SUMPRODUCT type functions, as I need to produce tens of thousands of these summaries (the fields have a lot of segments) and the calculations take far too long to be feasible, even when restricting the arrays to only non-blank rows.
Bookmarks