Mac Forum / Applications / Excel / March 2006

Hello -

How you have your data structured is not clear enough to offer a clear-cut
solution to the problem. If you have the data arranged with the Years & the
Months listed in columns, let me offer a slightly different approach.

First, make sure your data is arranged in consecutive columns & rows,
preferably with Captions at the top of each column. I'm assuming you have a
separate column for Month & for Year.

Remove the Grouping you've already done & get rid of your Summary column -
you don't need it & it may be part of the problem.

Next, with your cell selector anywhere in the list of records, go to
Data>Sort & select your Year column for the first sort key, Month for the
second.

Then go to Data>Subtotals, select your Year column in the top list (At Each
Change In), Sum as the Use Function, and put a check in the box for the
column you want to subtotal (remove any other checks).

When you click OK you will not only have your annual subtotals at the end of
each year, you will also have the Grand total at the bottom of the list
*and* the Grouping will be perfectly applied so you can collapse & expand as
you wish. You will also be able to go back into the Subtotals dialog and
turn the subtotals Off any time you want, then go back & turn it on again.
The dialog will remember your settings as long as you don't change them for
some other field or operation & as long as the records are sorted in the
correct order.

If your data is arranged differently please post back indicating exactly how
it is structured.

HTH |:>)

On 3/23/06 2:33 PM, in article
4DEA9F13-3BB4-4E51-8E23-3C47B2214299@microsoft.com, "GreenInIowa"