How to get the reference to the cells of the current data group

[Paul Schwann]

Hi there,

I have the data in my worksheet grouped such that I can show & hide a
hierarchy by clicking the +/- icons on the very left side of the sheet. What
I am looking for now is a simple and easy way to sum the numbers in on of the
columns for each group. Of course, I can type a formulas like

=SUBTOTAL(9, A5:A8)

in each summary row (where A5:A8) is one of the subgroups. But with almost
100 subgroups which are also changing from time to time, this is quite
tedious. What I am looking for is something like:

=SUBTOTAL(9, CURRENTGROUP())

Where the ficticious function CURRENTGROUP() returns the range of all cells
(of the current column) in the current data group.

Is there something like it? Or could you share your ideas how you would
solve it?

Thanks!
Paul

 

[Shane Devenshire]

Hi,

take off the manual Grouping and use the Data, Subtotal command. It will
automaticall group your data and run subtotals.

Or you could use a pivot table.