Table Subtotals

The built-in Subtotals facility (now on the Data tab) has never been particularly useful. Inserting subtotal rows into your data rows breaks one of the commandments of Excel: Thou shalt not sully the purity of Thy Data. Not only does the data have to be sorted prior to subtotalling, you can not then re-sort or filter the data. Furthermore, with 2007, you can not use subtotals with Tables, which as I’ve mentioned previously, are rather useful.

So what are the alternatives? One way is to use an array formula as a calculated filter. Here’s an example:

As you can see, it’s an old workbook (2005), which I’ve ‘upgraded’. To get the subtotals by month, you compare the Date column with the two boundary values, and multiply with the Value column. D2 contains the following single-cell array formula:

{=SUM((Table1[Date]>=D1)*(Table1[Date]<E1)*Table1[Value])}

Obviously, Table1 should have a more meaningful name. As you might already know, we can’t use AND here, as this collapses the tests to a single Boolean value, rather than a column-sized array of Booleans. The trick is to treat the Booleans as 0/1 values and multiply them. So for row 2, we’ll have (1 * 1 * 54).

The formula then Fills Right. We could equally well have the subtotals vertically, and Fill Down.

If you don’t fancy the array formulas, here’s an alternative:

Here, I’ve added a Month column, to make the filtering simpler. B2, for example, contains:

=MONTH(Table2[[#This Row],[Date]])

Each of the subtotals is a DSUM formula, using the cells above as the criteria for the filter. E3 contains:

=DSUM(Table2[#All],”Value”,E1:E2)

Note that we need the [#All] accessor, otherwise the formula does not pick up the header row. Note also that the column name is a string, not a name, and is thus in quotes.

The formula Fills Right. A limitation is that the criteria ranges have to be vertical – that is, column label above criterion – so we can’t have the subtotals arranged vertically. However, for presentation purposes, we could use an array function to transpose the range from 3R x 4C to 4R x 3C:

{=TRANSPOSE(E1:H3)}

Although we were using the DSUM variant to avoid array formulas, TRANSPOSE is easy enough to understand.