OK, so let’s move onto another great formula – this one much underutilized amongst Excel users: SUBTOTAL.
SUBTOTAL has 2 useful advantages over the standard aggregation formulas (SUM, AVERAGE etc.), being:
1. If the SUBTOTAL encounters a cell with another SUBTOTAL formula, it will ignore it. This is in contrast with the standard SUM formula (for example) which will count that formula as part of the total.
2. SUBTOTAL responds to filtered data (and also to hidden rows though that is less useful). Whenever you have raw data I recommend using SUBTOTAL in the top row above the filter row to aggregate the metric data below.
Just these 2 features alone make it worth using in place of SUM, AVERAGE etc, particularly when analysing raw (tabulated) data sets – in this case, as mentioned in point 2 above, get into the habit of having the SUBTOTAL formula on the very top row, above the filter row, so that you can analyse the results of the filter.
Before Excel included the ‘Inconsistent adjacent formula’ warning, the first feature of SUBTOTAL mentioned above was useful for large matrices of presentation data where errors could occur in the SUM formulas for nested subsets of the data, which would then cause errors in the overall SUM totals. With SUBTOTAL these errors can exist in the subsets, but not impact the overall total.
So, strong recommendation is to start with ditching the SUM formula in favour of SUBTOTAL(9,…) as a starting point. The syntax of the formula is simple; for summing use:
=SUBTOTAL(9, <Sum Range>)
And here is some good material for getting more familiar with the formula:
Excel SUBTOTAL function with formula examples
How to Use the SUBTOTAL Function in Excel
Note that the use of the SUBTOTAL formula discussed here is slightly different to the Subtotalling feature in Excel where data is first sorted then subtotalling is implemented at the prescribed level/s, resulting in row +/- groupings.
