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
Table of Contents
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 |
