Subtotal
Possibly one of the most under used functions in Excel, subtotal is very useful in reports where you need to be sure that the subtotals through the report are not added into the grand total at the bottom of the report. Have a look at the picture on the right. How many times have you seen the grand total formula that refers to all the subtotals.
Where you would normally use the sum function and then add up each of the sums, Subtotal allows you to place a subtotal anywhere in the report and it will be excluded from all other subtotal functions. The formula to get the Grand totals of 97 in this picture are =C9+C15+C21 in the sum() column and =SUBTOTAL(9,F5:F22) in the subtotal() column.
Now imagine you have 20 or more subtotals and you can see how the subtotal function can be very useful.
In order to get the subtotal to add (sum) use the 9 option. SUBTOTAL(function_num, ref1, ref2, ...)
The other options available in the function are …
| Function_Num | Function |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
