As an accountant, one of the frequent tasks that needs to be accomplished is the summing up of results; this can be anything from summing up ledger balances to a full income statement or balance sheet. We normally use a SUM() function, but a better function is available; the SUBTOTAL() function.
Take an example of a balance sheet asset section below:
If you took a look at the formulas that were driving the total assets section, you would notice the format that is used by most financial analysts, where SUM is used for each section of the balance sheet, then an aggregate sum is calculated at the bottom.
In this example, the SUM() function meets the need. There are only 3 categories that need to be aggregated, and tracking all the sums at the bottom is not challenging. But what if there were 12 categories that needed to be aggregated? It would become much easier to forget to add a number!
This is where the SUBTOTAL() formula works wonders! The SUBTOTAL() function can apply a function to a set of numbers, but ignores the values of the SUBTOTAL() function. Instead of having to pick all the cells with the SUM() function for the aggregation, you can just pick the entire column!
How SUBTOTAL() works
The syntax for the SUBTOTAL() function is as follows:
=SUBTOTAL(function number, range, [more optional ranges])
The range section is just the list of cells that you are looking to create your total over – in the example in the balance sheet, it was all the cells on the balance sheet.
The function number needs a bit more explaining. A list of all the function numbers is available at the bottom of the post.
In most cases, you will be using the 109 function; this is used to calculate the sum over a range, ignoring hidden rows.
The other function that you may be using is the 9 function – this also provides the sum over a range, but if there are rows that are hidden, these rows will still be included.
It is worth mentioning that if a row is filtered out, it will be excluded in both the 9 and 109 functions.
Hiding the row will exclude using the 109 function, but not the 9 function. 
Filtering the row will exclude in both the 9 and 109 functions.
Enjoy using SUBTOTAL() in your future financial analysis!
| Function Number | Function Number | Function |
| (includes hidden values) | (ignores hidden values) | |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |