by **citadel5** » Sun Oct 26, 2014 7:20 pm

This is mostly copying and pasting but if you would like to avoid it, you can simply use the pivotData() function. This will generate the whole record/sums sorted report with just one (array) formula entered in sheet1, for example:

=pivotData(sheet2!D1:F1000000, {1},,{2,3}, {PIVOT::Sum,PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

or with two formulas for each column of sums:

=pivotData(sheet2!D1:F1000000, {1},,{2}, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

and

=pivotData(sheet2!D1:F1000000, {1},,{3}, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

(where the D1, E1 and F1 cells contain e.g. the following labels: "record number", "injury", "fatal".)

However the report will contain only those record numbers which occur within the sheet2!D1:D1000000 range. To display all record numbers (e.g. 1...n), you would need to add the series of 1...n numbers below sheet2!D1000000 and

change the above sheet2!D1:F1000000 to include these numbers.

The above will be slower than the previous solution, but still much faster than SUMIFs (for very large data sets).

If you need something like that in the future, please take a look at the corresponding help topics and the sample.gsc file, which contains a few pivot tables.