Categories

# Make the Subtotal Function Dynamic

You can use the SUBTOTAL function to calculate subtotals in a list or table of data. It can perform various functions, such as SUM, AVERAGE, COUNT, MAX, and MIN, while ignoring (or including) hidden rows or filtered-out values.

To quickly insert subtotals in a list or table, you can use the Subtotal command on the Data tab. This will automatically create subtotal rows and group them for easy viewing. If you want to manually create the subtotals, you can use the `SUBTOTAL` function with a reference that changes based on the filter applied.

## Subtotal Function

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 with AutoFilters applied to them. When the AutoFilter has been applied, the `SUBTOTAL` function will use only the visible cells; all hidden rows are ignored (but not manually-hidden rows). The operation it performs depends solely on the number (between `1``11` or `101``111` ) 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 shown in the following table:

Because you need to use only a number between `1` and `11` (or `101` and `111`), 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.

## Make Subtotals Dynamic with a Drop-Down List

1. Add all the function names, in the same order as in the above table, to a range of cells. For this example, we will use `D1:D11`.
2. 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.
1. Hide the `D` column. Select column `D` in its entirety and then right-click on it and choose `Hide` from the context menu.
2. Go to the `Developer` tab and click `Insert` from the `Controls` group, choose the `Combo Box` icon under the Form Controls section.
3. Click cell `C2`, to have your Combo Box automatically snap to the size of the column and row it resides in, hold down your `Alt` key at the same time as you size the Combo Box.
1. Right-click the Combo Box and choose `Format Control`.
2. The Format Control dialog box will displayed. Choose the `Control` tab and follow these instructions:
• In the `Input range`, type `Subs`.
• In the `Cell Link` box, type `\$C\$2`.
• In the `Drop down lines`, type 11.
1. In cell `C3`, enter this formula:
`=IF(\$C\$2="","","Result of "&INDEX(Subs,\$C\$2))`

In cell `C4`, enter this formula:

`=IF(\$C\$2="","",SUBTOTAL(\$C\$2,\$B\$2:\$B\$13))`

where `\$B\$2:\$B\$13` is the range on which the `SUBTOTAL` should act.

Now all you need to do is select the required `SUBTOTAL` function from the Combo Box and the correct result will be displayed, as shown in the figure.

## Subtotal

1. Insert Subtotals For a Range of Data
2. Format Subtotal Rows Using Conditional Formatting
3. Make the Subtotal Function Dynamic