SUBTOTAL Strangeness

Home/Data Handling, Excel 2007, Excel 2013/SUBTOTAL Strangeness

Introducing SUBTOTAL

SUBTOTAL is an incredibly useful function for producing reports … yet it’s relatively unknown.

If you have a list of values that are in categories and you want a subtotal for each category, then a grand total at the bottom, it’s so much better & far more reliable than using SUM.

The key difference is that SUBTOTAL ignores all other SUBTOTAL functions.  This means you can have one subtotal or a hundred subtotal functions, it makes no difference to the grand total at the bottom because SUBTOTAL will add everything up without double-counting.

Let’s say you want to have 3 subtotals in your data at A3, A10, and A15.  If you create these with SUBTOTAL at each row, your grand total function can simply read =SUBTOTAL(9, A1:A15).  If you used SUM instead, you’d need to write =A3+A10+A15

It’s not too bad when you only have 3 subtotals, but when you have 10+ SUBTOTAL is so much easier!

… just don’t mix SUBTOTAL and SUM together because that’ll cause problems!

Where SUBTOTAL gets weird

While the behaviour above is relatively straightforward, you may not expect it to work like this.

If you use the SUBTOTAL function inside another function e.g. =AVERAGE( SUBTOTAL(9, A1:A15), 55) function, any subsequent SUBTOTAL functions that include this one will still ignore the result.

This applies regardless of what the wrapper function is.  Depending on your workbook, this may or may not be what you were intending.

See the attached example to illustrate this concept.

2017-03-08T03:53:55+00:00 Data Handling, Excel 2007, Excel 2013|