What does it do?
It returns a Subtotal in a list or database
Formula breakdown:
=SUBTOTAL(function_num, ref1)
What it means:
=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)
***Go to the bottom of this post to see what each value stands for
SUMMARIZE DATA WITH DYNAMIC SUBTOTALS...
The Subtotal function can become dynamic when we combine it with a drop down list.
This is a great trick and one that can be used when creating an Excel Dashboard that summarizes key data metrics on one page.
STEP 1: We need to list the Subtotal summary functions in our Excel worksheet
STEP 2: In the ribbon select Developer > Insert > Form Controls > Combo Box
STEP 3: With your mouse select the region where you want to insert the Combo Box
STEP 4: Right Click on the Combo Box and select Format Control…
STEP 5: For the Input Range, you need to select the range with the Subtotal summary names from STEP 1
STEP 6: For the Cell Link, you need to select a cell where you want to show the output and press OK
(The Cell Link increments by 1 depending on the order of the list and the name chosen. We will use this value as our first argument in the SUBTOTAL function)
STEP 7: Enter the Subtotal function and for the first argument function_num we will reference the Cell Link from STEP 6
STEP 8: For the second argument, select the data range
So you can see as you choose a summary name from the drop down list, it gives us a value for the Cell Link which is equals to the function_num for that summary name!
***Values for the SUBTOTAL function_num:
Includes hidden values | Ignores 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 |