MrExcel.com - Your One Stop for Excel Tips & Solutions

Subtotals by Product Within Region

September 30, 2022 - by

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.

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.
Figure 724. 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.

There are four group and outline buttons to the left of column A. Subtotals have been added for Product and Region.
Figure 725. Excel adds two levels of subtotals.

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
Figure 726. #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.

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.
Figure 727. 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.

If you add subtotals to Product before Region, then there is a single XYZ total which spans some East XYZ and some Government XYZ.
Figure 728. 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.

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.
Figure 729. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Thomas Park on Unsplash