Excel: How to display more information when using Subtotals?

Question

I am looking for a way to show additional information (columns) when doing subtotals beyond the columns that you are using On Change and are doing a function to (sum, min etc).

A simple example, I have the following data, which has 5 columns of information:

Zip Code    Media Sunday Zip Code Circulation
3082    Bluebell 3,456,284 551
4418    SuperCoups 632,161 1,437
3221    AJ Direct 6,777,636 1,015
3240    AJ Direct 6,777,636 554
3768    AJ Direct 6,777,636 712
3774    AJ Direct 6,777,636 824
4219    Target Marketing 7,256,964 655
4418    Target Marketing 7,256,964 703
4434    Target Marketing 7,256,964 506

Normally we would want to use the subtotal feature to sum up the zip code circulation for each Media program. The problem, when we do this, the sub total view only shows the media name, and the summed up zip code circulation. On this summary view, I would also like to show the fields that also do not change, such as the Sunday circulation. Of course we can click on the + field to expand to show the details, but I'm looking for way to show additional columns of data that are not changing on the summary view.

Any idea how to do this?

Answer 1

Have you tried pivot tables?

You can add columns that contain additional 'formulas' too, using Calculated Fields or Calculated Items.

Bill

Answer 2

Can't you just change that sum type for that column to MAX or something like that? If the data for the every row is the same (for your subtotal/summary), then using an aggregate type like that might do what you're looking for.

UPDATE: It looks like there's no front-end way to aggregate two different ways at the same time (MAX on C, SUM on D), so here's what you do:

1. Create the subtotals as SUM on C and D when B changes
2. Highlight column C
3. Do a Find-Replace and replace "SUBTOTAL(9," with "SUBTOTAL(4,"

This will switch the SUM aggregates in that column to be MAX instead, which is what you're looking for. I've asked someone who ought to be an Excel MVP, and he said there's no way to do this without modifying the formula afterwards.

Hope this helps!

Answer 3

The easiest way to achieve this is to concatenate all of the required info into a single cell and use that as your "For each change in" column.

So if Zip Code was in column A, Media in column B and Sunday in column C then you could insert a new column as column A which would push the original three columns over (to B, C and D respectively). If you use this formula in A2:

=B2&" "&C2&" "&D2

and then autofill downwards you should now be able to get the desired info when using subtotals. It doesn't look very good but it's quick and easy

(The real problem is that your data is not normalised)