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.

4 comments
jbenton
jbenton

SUBTOTAL() will always evaluate as an array function so if one of your range references is like A1:A99="Y" then there's no need to press CTRL-SHIFT-ENTER btw you might want to use this array funtionality to create a COUNTIF/SUMIF type function for multiple conditions eg SUBTOTAL(9,range1=condition1,range2=condtition2,range3) will sum only the values in range3 that meet the specified criteria in range1 and range2

jbenton
jbenton

if you're writing the formulae longhand, SUBTOTAL() can be made to ignore or include hidden values; just add 100 to the aggregate function so if you want to count just visible numeric cells, use SUBTOTAL(103,ranges) [unless you've used autofilter, in which case values hidden by this will always be ignored] it's also worth noting that SUBTOTAL() ignores any nested subtotals in its specified ranges (so no double counting) and finally, if you're having trouble remembering the order of aggregate fucntions, they're alphabetical (took me ages to spot that one! - only when i wrote out a crib list that i noticed it)

Shadeburst
Shadeburst

My favorite is SUMIF to summarize a list of transactions by transaction type. Say for example col A is Date, B is Type, C is Reference, D is Amount. Types = BAL, INV, CRN, PAY, JRN. Below the data create a vertical list of the Types in col B and in cell D110 insert this formula (data runs from Row 2 to Row 100, summary starts on Row 110): =SUMIF(B$2:B$100,B110,D$2:D$100) and copy it downward. The result will look something like BAL 999,999.99 INV 99,999.99 CRN 99,999.99 JRN 99,999.99 PAY 99,999.99 (Apologies for layout, this text box doesn't seem to support tabbing)

Editor's Picks