I find SUBTOTAL to be a wild card function, so to speak. You can use SUBTOTAL in many different ways (Adding, counting, averaging, etc.), but the most usefulness I get out of this function is to use it in a long list that has filters applied because the SUBTOTAL function ignores hidden values.
Lets get Practical
Lets look at the following table
In the table above, both SUM and SUBTOTAL gives us the same result. Now, lets filter out any products containing an odd number!
See what happened? The SUM function calculates the total for the entire list, but SUBTOTAL ignores what we’ve filtered (aka hidden values) and gives us totals for only what is visible.
The Syntax
Great thing about SUBTOTAL is you can use it for lots of different calculations other than summing. Lets look at the function’s components:
=SUBTOTAL(function number, value reference)
Function number – Here you enter a number indicating which math operation you want to use for the SUBTOTAL. You can find averages, count blank or non-blank cells, add, or any number of other things. The table below tells you what each of the most common number functions do:
|
Function Number |
Function |
|
1 |
AVERAGE |
|
2 |
COUNT |
|
3 |
COUNTA (Count non-blank cells) |
|
4 |
MAX (Finds largest number) |
|
5 |
MIN (Finds smallest number) |
|
6 |
PRODUCT |
|
7 |
STDEV |
|
8 |
STDEVP |
|
9 |
SUM |
|
10 |
VAR |
|
11 |
VARP |
Value reference – This is the range of data you want SUBTOTAL to analyze.
Lets see how SUBTOTAL was used in our example:
You’ll notice that I used function number 9, SUM in order to get the totals for my list. This function is really useful for me when I’m analyzing large quantities of data because I can get live totals as I’m filtering.