First, to clear up any confusion, I’m talking about the Subtotal function, not the feature. A function in Excel is a command used in writing a formula. The Subtotal feature automatically inserts It subtotals (using the subtotal function) into a table of data.
Now I know it sounds strange that the SUBTOTAL function in Excel makes grand totals easier. You would assume from the name that it would make subtotals easier right? But the purpose of the subtotal function is to identify the number as a subtotal so that in can be ignored when adding up (or performing other calculations on) a column of numbers. Let’s look at a simple example.
The picture on the left shows the subtotal formula. The picture on the right shows the results as you would normally see them. Notice that on the grand total line you can include the entire range of cells above it. If you used SUM(B1:B10) the total would be doubled because invoice amounts and the location totals would both get included in the result. But the SUBTOTAL function knows to ignore other subtotals that fall within the range. This is what makes the SUBTOTAL function worth using. Without it you would have to write a formula pointing to each subtotal, as in =B4+B9 or sum the individual ranges, as in =SUM(B1:B3,B6:B8). The bigger your worksheet the more you’ll appreciate the convenience of SUBTOTAL().
The format for the subtotal function is SUBTOTAL(function_number, range of cells). You can include multiple cell ranges by separating them with commas.
The Function Number tells Excel what you want it to do with with the range of cells. 9 represents SUM but you’re not limited to just adding numbers. The options include:
| Function_number (acts on all values in the range) |
Function_number (ignores hidden values in the range) |
The number tells Excel to use this 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 |
At the beginning I mentioned the subtotal feature that automatically inserts subtotals and grand totals into a table. So why would you ever want to manually use the subtotal function? The subtotal feature is great but, it requires your data to be laid in a a very specific manner to work properly. So when your data doesn’t fit that pattern, it’s time write your own formulas using the use the SUBTOTAL function.
