Most of the time in our reporting we need to do subtotals followed by Grand total at the end of report. I have seen many people using SUM function for subtotals in between and then adding individual cells for calculating grand total at the end. There is nothing wrong with this method if you use it very carefully so that you do not miss any item while calculating grand total at the end.
However better approach is to use Excel Subtotal function for calculating Subtotal and Grand Totals.
Image above is showing column B with the Subtotal formula and resultant values with this formula. You may notice that when we use subtotal function for calculating Grand Total, it ignores the values resulting from subtotal function within its range. This method ensures that we do not miss any item while doing Grand Totals for our reports thus higher accuracy.
This becomes more helpful when you edit your report after some time to insert another subtotal. You do not need to remember to correct the final formula calculating the Grand Total.
Another advantage of using Subtotal function is that you need not to recalculate your formulas when used with filteres. This function ignores the values hidden by the filters and considers the values in the visible rows only.
Subtotal can be used for various type of calculations based on what do you want this fucntion to do for you. Syntex for this function is
SUBTOTAL(function_num, ref1, ref2, ...)
Following function numbers can be used in Subtotal function
I have used "9" in my example above for sum you can change it as per your required working.
However better approach is to use Excel Subtotal function for calculating Subtotal and Grand Totals.
Image above is showing column B with the Subtotal formula and resultant values with this formula. You may notice that when we use subtotal function for calculating Grand Total, it ignores the values resulting from subtotal function within its range. This method ensures that we do not miss any item while doing Grand Totals for our reports thus higher accuracy.
This becomes more helpful when you edit your report after some time to insert another subtotal. You do not need to remember to correct the final formula calculating the Grand Total.
Another advantage of using Subtotal function is that you need not to recalculate your formulas when used with filteres. This function ignores the values hidden by the filters and considers the values in the visible rows only.
Subtotal can be used for various type of calculations based on what do you want this fucntion to do for you. Syntex for this function is
SUBTOTAL(function_num, ref1, ref2, ...)
Following function numbers can be used in Subtotal function
I have used "9" in my example above for sum you can change it as per your required working.
| Function_num (Incl hidden values) | Function_num (Excl hidden values) | Function |
|---|---|---|
| 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 |
