Excel’s Subtotal feature calculates subtotals and grand totals for columnar data, but that’s not all it does. The feature supports eleven calculating processes: sum, count, average, maximum, minimum, product, count number, and more. The good news is that the feature requires very little set up – in less than a minute, you can render a rather complex problem into results.
For instance, suppose you need to count grouped items. You can use functions, but it might take you a while to come up with just the right combination. If your boss is standing there tapping her foot, you need something a bit quicker. That’s where Subtotal comes in.
Now, let’s look at a simple example. Specifically, let’s use Subtotal to return a count of the number of books due back on each date in the Due column in the sheet below.
The first step is to sort by the grouping column – that’s essential. In this case, that’s the Due column.
After sorting by the grouping column, you’re ready to start counting, as follows:
- Select any cell in the grouping column.
- Click the Data tab.
- Click Subtotal in the Outline group. In Excel 2003, Subtotals is on the Data menu.
- In the resulting dialog, choose Count from the Function dropdown.
- Click OK and Excel will display a subtotal for each date in the Due column.
The results are a bit unwieldy, but they’re quick. In addition, most users tend to use this feature with two columns—when column A changes, calculate column B. They don’t realize they can use this feature on a single column of data, as I’ve done.
