Learn How To Create Subtotals By Product Within Region With This MIcrosoft Excel Tutorial
This seems easy, but there is a trick to it. You want to add subtotals to the less detailed field first.
-
Sort by product within region, as shown in Fig. 706.
-
Add a subtotal by Region, as shown in Fig. 707.
-
Select Data – Subtotals again. Change Region to Product. Be sure to uncheck the box for Replace Current Subtotals, as shown in Fig. 708.
You now have two sets of subtotals. As shown in Fig. 709, there are now four Group & Outline buttons to the left of cell A1.
There are also two grand total lines, as shown in Fig. 710.
This is somewhat of a bug. It is easy enough to remove the second to the last line to remove the extra grand total, as shown in Fig. 711.
If you choose the #2 Group & Outline button, you will have totals by Region, as shown in Fig. 712.
If you choose the #3 Group & Outline button, you will have totals by Region and Product, as shown in Fig. 713.
Here is why it is important to do the subtotals in the right order. Say that your company sells three products. The Government region only buys product XYZ. You might have data that looks like the data in Fig. 714. Note that row 15 contains an XYZ record for the East and row 16 contains an XYZ record for the Government region.
If you subtotal by product first, the XYZ products from the East and the Government will be trapped in one subtotal in row 25, as shown in Fig. 715. This is an absolute mess.
If you then total by Region, you will have set up Groups that make no sense. Note that the XYZ total in D32 is greater than the Government Region total in D31, as shown in Fig. 716.
In Excel 95, there was no workaround for this problem. In Excel 97, they added the rule that XYZ rows separated by a blank row would be handled OK. Thus, you need to add subtotals by Region first.
You can create very powerful summary reports using two sets of subtotals. Remember to subtotal the outer grouping first and then the inner subtotals. On the second and subsequent calls to the Subtotal command, remember to uncheck the Replace Current Subtotals option.
Images
