Summarize Data With Dynamic Subtotals

Author: - Posted on December 10, 2015

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.


DOWNLOAD EXCEL WORKBOOK

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!

 

***Values for the SUBTOTAL function_num:

Includes hidden values    Ignores hidden values    Function  
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

HELPFUL RESOURCE:

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Post Reviews

5
5.0 rating
5 out of 5 stars (based on 11 reviews)
Excellent100%
Very good0%
Average0%
Poor0%
Terrible0%

Array formula

5.0 rating
September 12, 2020

Array formula make my work easy.I mean time saving.

Subash

Informative

5.0 rating
September 12, 2020

I was trying to do this from last 20 min’s but this post has just save the time

aman

Superb

5.0 rating
September 12, 2020

helpful in job.

afzal shaikh

Great Podcast

5.0 rating
September 11, 2020

I really enjoy listening to the podcast.

Francisco Llorente

Nice info and presentation

5.0 rating
September 10, 2020

It has helped me

Deepak Jain

Leave a Review

Leave a Comment

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

  • [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]
    [l]