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.

About these ads

2 Responses to “Table Subtotals”


  1. 1 Jon Peltier March 28, 2010 at 1:00 pm

    I’ve never used the subtotal function myself, because I was never sure (or never trusted) what was being subtotaled and how the subtotals were totaled. And it broke up the sanctity of my data.

    My philosophy is that, if my data is properly structured, I could make a pivot table from it. I could use the built in subtotal features of the pivot table, but then, instead of having just subtotals, I also could pivot the data. Much more powerful and flexible, and the original data is intact.

  2. 2 Roy MacLean March 29, 2010 at 9:06 am

    Yes, of course, a pivot table is a very flexible way of doing subtotals. However, one reason for wanting subtotals might be for presentational purposes – i.e. on a worksheet that you intend as a printable report, with free text, nice formatting, etc. Having a way of doing subtotals as cell-by-cell formulas would give a bit more flexibility here.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




March 2010
M T W T F S S
« Feb   Apr »
1234567
891011121314
15161718192021
22232425262728
293031  

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: