Summarize Data With Dynamic Subtotals

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


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

Subtotal Summary Names

STEP 2: In the ribbon select Developer > Insert > Form Controls > Combo Box

Excel 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…

Excel 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

Excel Subtotal 1st argument

STEP 8: For the second argument, select the data range

Excel Subtotal 2nd argument

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!

Subtotal Dynamically

***Values for the SUBTOTAL function_num:

Includes hidden values     Ignores hidden values     Function  
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Related Posts

Sum a Range Using the INDEX Function You can sum a range of values within a table using the INDEX function in Excel.  This is valuable when you want to extract key metrics from a table and put them in an Excel Dashboard. To make this work you firstly need to start your Excel formula with the SUM function followed...
VLOOKUP Example: Vlookup with a Drop Down List What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...
Advanced SUMPRODUCT Function: Maximum Sales What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function. Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX fu...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list? Do you want to make them uniform by adding leading zeros to them? Well, it's a pain to add zeros in front of them one by one! Thankfully, Excel allows you to add leading zeros with one singl...

Leave a Comment

Your email address will not be published. Required fields are marked *