Excel Subtotal Function Dynamic
Although SUBTOTAL is one of Excel's most convenient functions, you sometimes want to choose the function it uses, or apply it to data that can expand and contract.
You use the SUBTOTAL function in Excel to perform a specified function on a range of cells that have had AutoFilters applied to them. When the AutoFilter has been applied, the SUBTOTAL function will use only the visible cells; all hidden rows are ignored. The operation it performs depends solely on the number (between 1 and 11) that you supply to its first argument, Function_num. For example:
=SUBTOTAL(1,A1:A100)
will average all visible cells in the range A1:A100 after AutoFilters have been applied. If all rows in A1:A100 are visible, it will simply average them all and give the same result as:
=AVERAGE(A1:A100)
The number for the first SUBTOTAL argument, Function_num, and its corresponding functions are as shown in Table below.
SUBTOTAL function numbers and their corresponding functions| Function_Num | Function |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
Because you need to use only a number between 1 and 11, you can have one SUBTOTAL function perform whatever function you choose. You even can choose from a drop-down list that resides in any cell. Here is how to do this.
Add all the function names, in the same order as in Table above, to a range of cells. For this example, we will use D1:D11. With this range selected, click the Name box (the white box on the left of the Formula bar) and type the name Subs. Then click Enter.
Select column D in its entirety and then right-click and select Hide. Now, select Developer → Controls → Insert → Form Controls (pre-2007, View → Toolbars → Forms), click the ComboBox control, and then click cell C2.
Use the size handles to size the ComboBox so that it can display the longest function name-i.e., AVERAGE.
To have your ComboBox automatically snap to the size of the column and rowit resides in, hold down your Alt key at the same time as you size the ComboBox.
Right-click the ComboBox and choose Format Control, then the Control tab. In the Input range, type Subs. In the Cell-Link box, type $C$2. Now change the drop-down lines to 11. In cell G1, enter this formula:
=IF($C$2="","","Result of "&INDEX(Subs,$C$2))
In cell G2, enter this formula:
=IF($C$2="","",SUBTOTAL($C$2,$A$4:$A$100))
where $A$4:$A$100 is the range on which the SUBTOTAL should act.
Nowall you need to do is select the required SUBTOTAL function from the ComboBox and the correct result will be displayed.
