Results 1 to 12 of 12
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #2
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel (9.0.6926 SP-3)

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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel (9.0.6926 SP-3)

    Data - subtotals
    <remove all>

    Data - subtotals
    At each change in
    Product
    Use function:
    SUM
    Add subtotal to:
    [Add fields desired]
    <ok>

    Data - subtotals
    At each change in
    Region
    Use function:
    SUM
    Add subtotal to:
    [Add fields desired]
    Uncheck "replace current subtotals"
    <ok>

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

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #11
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •