Display multiple subtotaling functions in Excel

You probably know that Excel’s Subtotal feature quickly generates subtotals for groups. What you might not know is that Subtotal can evaluate more than one column using the same subtotaling function. In addition, it can evaluate the same group using a different function.

Two demo Excel files are available for you to download.

Multiple subtotaling functions

First, let’s use Subtotal to display multiple Average functions for the same group. Specifically, we’ll use this feature to return the average for both the Unit Price and Units In Stock fields in the data shown below:

1. First, sort the data by the column that contains the grouping value. Because Subtotal evaluates group, you should always sort before executing the feature. In this case, sort by the Category values to evaluate the many category groups.
2. Click anywhere in the data. Click the Data tab and then click Subtotal in the Outline group. In Excel 2003, choose Subtotals from the Data menu.
3. In the resulting dialog box, choose Category from the At Each Change In dropdown and Average from the Function dropdown.
4. Here’s how to get multiple functions: in the Add Subtotal To list, check Unit Price and Units In Stock. If anything else is checked, uncheck it.
5. Check the Replace Current Subtotals and Summary Below Data options.
6. Click OK.

How easy was that? With just a few clicks, you generated the average price and stock for each product category. You could add more averages if you wanted to. What you can’t do is add a different function to the same subtotaling row.

Multiple subtotaling rows

When you need to display different subtotaling functions, add another row of subtotals. For instance, let’s sum the number of units on order – without deleting the existing row of averages:

1. You sorted the data earlier by Category, so you don’t need to sort again.
2. Click anywhere inside the data and click Subtotal (in the Outline group). In Excel 2003, choose Subtotals from the Data menu.
3. This time, select Sum from the Function dropdown.
4. Uncheck the Unit Price and Units In Stock in the Add Subtotal To list and check Units In Stock.
5. This next step is very important – do not skip it. Uncheck the Replace Current Subtotals option.
6. Click OK.

Now you have two subtotaling rows. The first shows two averages and the second shows the total number of units in stock.  It’s such a simple step, but the return is huge!

Subtotals in the same row will always be of the result of the same function. In this example, we displayed two Average functions in the first row. When you want to evaluate the same group, but display a different function, add a new row. Just remember to uncheck the Replace Current Subtotals option (step 4) when doing so – that’s the key to displaying different subtotaling functions for the same groups.

To learn more about Excel’s Subtotal feature, read 10+ tips for working with Excel’s Subtotal feature.