Subtotals by Product Within Region
September 30, 2022 - by Bill Jelen
![Subtotals by Product Within Region Subtotals by Product Within Region](/img/excel-tips/2022/09/subtotals-by-product-within-region.jpg)
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.
![A data set has Subtotals by Region. Invoke the Subtotal command again, this time, choose At Each Change in Product. Unselect the box at the bottom for Replace Current Subtotals.](/img/content/2022/09/LE10000687.jpg)
Results: You now have two sets of subtotals. There are now four Group & Outline buttons to the left of cell A1.
![There are four group and outline buttons to the left of column A. Subtotals have been added for Product and Region.](/img/content/2022/09/LE10000688.jpg)
If you choose the 3 Group & Outline button, you will have totals by region and product.
![Click the Number 3 Group and Outline button and you see Product Totals, Region Totals, and a Grand Total](/img/content/2022/09/LE10000689.jpg)
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.
![Showing a scenario where Subtotals will be wrong if you don't do them in the proper order. The East region ends with products DEF, then XYZ. But the next region - Government, only buys XYZ. This puts the first Government XYZ record immediately after the last East Region XYZ record.](/img/content/2022/09/LE10000690.jpg)
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.
![If you add subtotals to Product before Region, then there is a single XYZ total which spans some East XYZ and some Government XYZ.](/img/content/2022/09/LE10000691.jpg)
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.
![Continuing the bad example of how *not* to do Subtotals, if you then add totals by Region, the XYZ Total spans East XYZ, East Total, Government XYZ, and Government Total.](/img/content/2022/09/LE10000692.jpg)
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.
This article is an excerpt from Power Excel With MrExcel
Title photo by Thomas Park on Unsplash