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.

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 |