This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Subtotals by Product Within Region
Problem: I want to add subtotals by two fields, such as Product and Region.
Strategy: Adding subtotals by two fields seems easy, but there is a trick to it. You need to add subtotals to the least detailed field first. Here’s how it works:
1. Sort by product within region. Select a cell in the Product column. Click the AZ icon on the Data tab. Select a cell in Region. Click AZ.
2. Select Data, Subtotal and add a subtotal by Region.
3. Select Data, Subtotal again. Change Region to Product. Be sure to uncheck the Replace Current Subtotals box.
Figure 665 Uncheck Replace Current Subtotals.
Results:You now have two sets of subtotals. There are now four Group & Outline buttons to the left of cell A1.
Figure 666 Excel adds two levels of subtotals.
If you choose the 3 Group & Outline button, you will have totals by region and product.
Figure 667 #3 group and outline view.
If you choose the 2 Group & Outline button, you will have totals by region.
Additional Details: Here is why it is important to do the subtotals in the correct order: Say that your company sells three products. The Government region buys only product XYZ. You might have data that looks like the data below. Note that row 15 contains an XYZ record for the East, and row 16 contains an XYZ record for the Government region.
Figure 668 Same product, different regions are adjacent.
If you subtotal by product first, the XYZ products from the East and the Government regions will be trapped in one subtotal in row 25. This is an absolute mess.
Figure 669 Subtotal product first, and Excel has no idea that you will later subtotal by region.
If you then total by region, you will have set up groups that make no sense. Note that the XYZ total in D32 includes both Govt and East records.
Figure 670 Chaos ensues.
Additional Details:In Excel 95, there was no workaround for this problem. In Excel 97, Microsoft added the rule that XYZ rows separated by a blank row would be handled correctly. Thus, you need to add subtotals by region first.