Can't get started with Subtotal

[niravrph]

Hi,


I just can't seem to figure out the Subtotal function. I have my data configured this way:


Russian books Chinese books

Area Metro

East NY

Queens, NY 0 400

Brooklyn, NY 500 300

Brooklyn Heights, NY 0 500

Manhattan, NY 200 200

East Detroit

Detroit North 500 0

RCM Northern Plains

Minnesota 300 0


I want to subtotal on the Metro, as there are multiple cities in each Metro. So essentially at the end of the day I want EACH metro to have the ability to expand/collapse - and then have a Grand Total for Russian & Chinese books at the bottom of the page.


Can someone show me how to do this? I tried but don't seem to get it. I can send you the Excel file if you want as I know the web won't show the data properly. Let me know how I can do this/where to send it to.


Thanks,

NIRAV

 

[Luke M]

Unless you want to add a helper column that uses a common name to identify groups (where you want the subtotals), you'll need to setup the groupings manually.


For your layout, it looks like you want to group things "upward", so we need to change the default setting. Go to Data - Group and Outling, settings. Uncheck the "Summary rows below detail" box. Ok out.


Now, insert a row below each group that you want a subtotal. Create the subtotal formulas (function number 9 or 109, depending on if you want to include hidden rows).


Now, to do you groupings, you can select the rows you want to group, then press Alt+Shift+right arrow (to ungroup, use left arrow)