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

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:

If you like this Excel tip, please share it

email

Pinterest

Facebook

Twitter

Linkedin