Tech Tip: Excel and Subtotals

clive All, Excel, Tech Tip

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.