Software

Harness the power of Subtotal in Excel to count grouped items

Excel's Subtotal feature can render quick results when you need simple grouping calculations. Susan Harkins shows you how this powerful feature works.

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:

  1. Select any cell in the grouping column.
  2. Click the Data tab.
  3. Click Subtotal in the Outline group. In Excel 2003, Subtotals is on the Data menu.
  4. In the resulting dialog, choose Count from the Function dropdown.
  5. 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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks