If you use SUM() in Excel to calculate totals, you may want to know about the SUBTOTAL() function, which does a similar job, butbetter. SUBTOTAL takes 2 arguments – the first is a ‘magic number’, that tells it what you want to do; the second is a range of data to be totalled. Here’s what the magic numbers do:
- 1 – Average
- 2 – Count
- 3 – CountA
- 4 – Max
- 5 – Min
- 6 – Product
- 7 – Stdev
- 8 – Stdevp
- 9 – Sum
- 10 – Var
- 11 – VarP
If you add 100 to the magic number, excel will ignore hidden rows – so, 109 will give a sum of the visible rows in the range – very useful when used with a autofilter, so that the sum will reflect the rows that have been selected.
Furthermore, if you have used the Subtotals menu option to create, for example, a yearly total in the middle of the data, a SUBTOTAL() call will ignore those subtotal results in the final data.