# Thread: Total of subtotals (Excel 9.0.6926 SP-3)

1. ## Re: Excel (9.0.6926 SP-3)

Subtotal all the data together. (subtotals ignore other subtotals)

If you use the subtotals feature it is listed as a grand total. You can also just sum them up normally.

Steve

2. ## Re: Excel (9.0.6926 SP-3)

I still don't get it.

I have the sub-totals for each of my Regions for each of my Products and there is a Grand Total at the end.

What I also need is the Total for each of my Products. (Sub-total of Region A + Sub-total of Region B = Product Total) <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

3. ## Re: Excel (9.0.6926 SP-3)

Add a another subtotal using the data - subtotals and choose the other field.
[note: make sure that "replace current subtotals" is unchecked]

Steve

4. ## Re: Excel (9.0.6926 SP-3)

What other field? (I've attached the file)

5. ## Re: Subtotals (9.0.6926 SP-3)

Won't that just give a subtotal of each product within each region, as opposed to a subtotal of each product for all regions (which I think is what Colin is after)?

Perhaps there's another way, but you may need to resort the data and do another set of subtotals on the Product this time as opposed to the region.

A Pivot Table would also do quite nicely for this, I think.

6. ## Total of subtotals (Excel 9.0.6926 SP-3)

Subject edited by HansV to something slightly more specific than 'Excel'

How do I get totals of sub-totals? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

7. ## Re: Excel (9.0.6926 SP-3)

What you (probably) need is a pivot table. Select cell A1, then select Data | PivotTable and PivotChart Report... and follow the instructions. In the layout, you should put Product in the row area, Region in the column area, and any number of fields in the data area. You may need to double click them to get the sum.

8. ## Re: Excel (9.0.6926 SP-3)

Data - subtotals
<remove all>

Data - subtotals
At each change in
Product
Use function:
SUM
<ok>

Data - subtotals
At each change in
Region
Use function:
SUM
Uncheck "replace current subtotals"
<ok>

I agree with Hans, that a pivot table will summarize better all in one place.

Steve

9. ## Re: Excel (9.0.6926 SP-3)

I'm not sure about the Pivot Table. If you can look at my example file what I really want, after row 66 is a Total for 10607/2909 (Total Region E + Total Region W = Total Product 10607/2909.)

10. ## Re: Excel (9.0.6926 SP-3)

Steve's instructions will do what you want. The pivot table is another way of summarizing data - it's more compact, but doesn't show the individual data, just the (sub)totals.

11. ## Re: Excel (9.0.6926 SP-3)

That is so wierd! And so simple! Exactly what I want. I thought I had tried that before but I was getting double totals.

Thanks guys!

<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

12. ## Re: Excel (9.0.6926 SP-3)

If you do it in the reverse order (regions, then products) you will get different results:
It will subtotal first on regions then it will add the subtotals of the products within the regions. You wanted the opposite, subtotal on products and then the regions within products.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•