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:
- 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.
- Select Data, Subtotal and add a subtotal by Region.
- Select Data, Subtotal again. Change Region to Product. Be sure to uncheck the Replace Current Subtotals box.
- 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.
- Excel adds two levels of subtotals.
If you choose the 3 Group & Outline button, you will have totals by region and product.
- #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.
- 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.
- 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.
- 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.