The Excel SUBTOTAL Function - Calculations on Filtered & Visible Cells Only
15

The Excel SUBTOTAL Function – Calculations on Filtered & Visible Cells Only

Bottom line: Learn how the SUBTOTAL function works in Excel to create formulas that calculate results on the visible cells of a filtered range or exclude hidden rows.

Skill level: Beginner

Excel SUBTOTAL Function Calculations on Visible Cells of Filtered Range

The SUBTOTAL Function Explained

The SUBTOTAL function is a very handy function that allows us to perform different calculations on a filtered range.  The most common use is probably to find the SUM of a column that has filters applied to it.

The SUBTOTAL function will display the result of the visible cells only.  This is great for tying out numbers in a filtered column to summary reports and pivot tables.

Excel SUBTOTAL Function Calculates Metrics on Visible Cells of Filtered Range

SUBTOTAL actually gives us eleven (11) different functions to choose from.  We can use it to return the SUM of the filtered range, or any of the following calculations.

  1. AVERAGE
  2. COUNT
  3. COUNTA
  4. MAX
  5. MIN
  6. PRODUCT
  7. STDEV.S
  8. STDEV.P
  9. SUM
  10. VAR.S
  11. VAR.P

That means we can use SUBTOTAL for any of the different calculation types listed above.  However, only ONE of the calculation types can be used in the formula to return the result to a single cell.

How to Write a SUBTOTAL Formula

The SUBTOTAL function is pretty simple.  It just has two required arguments.

Argument #1 – function_num

The first argument is the function number.  This allows us to specify which of the eleven calculation types we want to use, and we can select the number from the drop-down list in the formula editor.

Excel SUBTOTAL Function Step 1 Function_Num Argument for Calculation Type

To select an item in the list, double-click it or press the Tab key.

Quick Tip: If the drop-down list disappears, you can press Alt+Down Arrow to view it again.

You probably noticed that the calculation types are listed twice.  One set that is single digit number, and another set that stars with 101.

  • The 1-11 functions will include hidden rows
  • The 101-111 functions will ignore hidden rows.

Hidden rows are different from filtered rows.  Both calculation types will excluded rows that are not visible due to filtering.  However, if we were to hide additional rows in a filtered range then the 1-11 functions would still include these hidden rows in the result.  The 101-111 functions would not include the hidden rows.

So, if you only want to see the results for visible rows, regardless of how the rows are hidden, then use the 101-111 functions.

The argument is specified by typing the single number 1-11, 101-111 in formula.  When we select an item from the drop-down list, it will automatically put the number in the formula.

We can also reference a cell that contains the number of the function.  This can be used for a dynamic formula where you might want the user to select the metric from a drop-down list.

Argument #2 – ref1

The second argument is the range (column) of cells we want to perform the calculation on.  We can simple select the range of cells or type in the range reference.

Excel SUBTOTAL Function Step 2 ref1 Select the Range

This range will typically be a single column that has filters applied to it.

Don’t forget to make the column and/or row references absolute (F4 key), depending on if you are going to copy the formula down/across.

Additional ref Arguments

We can also add additional ranges to the formula.  We can include additional columns that are not contiguous (next to each other), by adding additional ref ranges to the formula.  These range references are separated by a comma.  We can add up to 254 additional range references.

Excel SUBTOTAL Function Additional Ref Ranges

Apply Filters To See The Calculation on Visible Cells

Now that we have the SUBTOTAL function written, we can apply filters to multiple columns in the range.  The SUBTOTAL function will return the results of the visible cells only.

SUBTOTAL Apply Filters to View Calculation on Visible Cells Only

Excel Tables also use the SUBTOTAL function in the Totals Row of a Table.  You can select one of the calculation types from a in-cell drop-down menu in the Totals Row, and the SUBTOTAL function will automatically be created for you.  This is one of the many time saving benefits of using Excel Tables.

Excel Tables Total Row Creates SUBTOTAL Formulas Automatically

Checkout my video on a beginner’s guide to Excel Tables to learn more about this awesome feature.

Bonus tip: The SUBTOTAL function can also automatically be created with the AutoSum feature when the range we are applying it to is filtered.

I also have an article and video on how to select and copy/paste the visible cells only.

The SUBTOTAL Metrics Macro

Sometimes we want to use the SUBTOTAL function to calculate more than one calculation type on a column.  For example, we might want to see the Sum, Min, Max, Count, and Average of the column for quick analysis.

Typing a SUBTOTAL formula for each calculation type can take some time.  So, I wrote a macro that creates all the formulas for us with just a few clicks.

The SUBTOTAL Metrics Macro creates a summary table on the sheet with a SUBTOTAL formula for each calculation type based on range that you specify.  Here is a quick screencast so you can see it in action.

Excel SUBTOTAL Metrics Summary Macro

I have a separate post that explains the macro in detail, including how to modify it for your needs.  You can also download the file that contains the macro there.

Click here to read the post on the SUBTOTAL Metrics Macro

What About the AGGREGATE Function?

There were a few comments posted below about the AGGREGATE function, so I’m adding a section here.

The AGGREGATE function was introduced in Excel 2010.  It works similar to the SUBTOTAL function, but has some additional features that allow us to ignore cells with errors values and other SUBTOTAL or AGGREGATE formulas in the ref range.  It’s another great function to add to your tool box.

Even though AGGREGATE has more features, it’s still good to know SUBTOTAL for a few reasons:

  1. If you have any users still on Excel 2007, then the AGGREGATE function will not work for them.  You have to use SUBTOTAL.
  2. SUBTOTAL is to AGGREGATE, as VLOOKUP is to INDEX/MATCH.  SUBTOTAL is like the baby brother of AGGREGATE.  Similar to the relationship between VLOOKUP and INDEX/MATCH.  My guess is that VLOOKUP is used over 95% of the time by users, even though INDEX/MATCH offers a lot of addtional benefits (checkout my free videos series on the lookup formulas to learn more about the differences).  The same is probably true with SUBTOTAL, especially since Excel uses the formula in a lot of different Excel features.
  3. Excel automatically generates the formulas, as I mentioned above, so it is very likely you will see this formula in an Excel file.  Excel creates formulas with the SUBTOTAL function in the following Excel features/commands:
    1. The Subtotal command (Data tab > Subtotal).
    2. The AutoSum command on a filtered range (Home tab > AutoSum or Alt+=)
    3. The Totals Row of a Table (Ctrl+Shift+T).

Excel likes to create these formulas for us, so it’s good that we know they work. 🙂

With all that said, AGGREGATE is still AWESOME, and one every Excel user should know.  I will do a separate post that explains the differences in more detail.

SUBTOTAL Versatility

The SUBTOTAL function is a great one to have in your Excel tool belt, especially when working with filtered ranges of data.

Do you have any tips or tricks for using the SUBTOTAL function?  Please leave a comment below with questions or suggestions.  Thank you!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 15 comments
Lschwa01 - February 20, 2017

Please add a section on copying the results. When I use the 2 option on the processed data I get the summary result lines. There’s a way to copy only those lines but I can’t remember it. Thanks for all of your tips.

Reply
shiva - February 10, 2017

Hi John,

Thanks for explaining the difference between 1-11 & 101 to 111.
When i tried to use the SUM formula using single digit it actually works on including hidden rows to the total only when the range is not filtered. If we apply any filters to the range it is considering only visible cells but not hidden rows.
Am i doing any mistake here.
Because normal sum formula would serve the purpose of including hidden rows.
Could you please explain.

Shiva

Reply
Carrie - February 10, 2017

Thanks for explaining the difference between 1-11 vs 101-111 function. I never could figure out the difference since I usually use the Alt + = on a table. Cheers~!

Reply
    Jon Acampora - February 10, 2017

    Thanks Carrie! Interesting enough, Excel uses the 109 function when you use AutoSum at the bottom of an Excel Table. If you use AutoSum at the bottom of a regular range that has filters applied, Excel uses function 9. Hmm… 🙂

    Reply
      THOMAS K. - August 16, 2017

      Hi Jon

      I have around 20 columns and valid 5000 rows in an excel sheet with filter enabled for each column.

      The first column ‘A’ is for entering the date (from 01 Jan to 31 Dec).
      For other columns I have a lot of criteria’s to add and filter if required.

      My subject is, in all rows of a specific column (let’s say column K) there will be several “YES”, “NO” or some digits “10”, “20” which is a result for certain tasks. I want to calculate and show the percentage of these results “YES”, “NO”, “10” or “20” in a specific cell in the title row. ie. If there is 50 nos of “YES” and 25 nos of “NO” and 25 nos of “10”, I want to display “YES”=50%, “NO”= 25% & “10”=25%. The important thing here is that, this percentage shown should not be the total calculated from the 5000 rows, BUT ONLY WHAT IS SHOWING FILTERED AS PER THE DATE’S COLUMN ‘A’. (means, the percentage shown should be as per the view mode)

      Can you help me?

      Reply
Kirk - February 10, 2017

Great information, Jon!

I have found that AGGREGATE function suits my needs better when I’m using large tables of data.

I’d like to hear your thoughts on SUBTOTAL vs. AGGREGATE.

Reply
    Jon Acampora - February 10, 2017

    Hi Kirk,
    Yes, AGGREGATE is another great function to know. I added a section above that explains some of the differences, and why it’s still good to know SUBTOTAL. I will write a separate post on AGGREGATE. Thank you!

    Reply
David - February 10, 2017

Jon,

Great tip.
Thanks for sharing the tip!

Reply
Juan - February 10, 2017

Thank you very much John for sharing this tip, it’s a pleasure to read such a great-explained trick!

Reply
MF - February 10, 2017

Nice blog post! Very detail in explaining the SUBTOTAL.

SUBTOTAL 103 can be used for creating a sequential number for visible rows only. Here’s the blog post:
https://wmfexcel.com/2015/05/09/sequential-number-for-visible-rows-only/

Hope you like it. 🙂

Reply
Matt - February 9, 2017

Why not =AGGREGATE()? What are the differences besides AGGREGATE having more options for ignoring cells.

Reply
    Jon Acampora - February 10, 2017

    Hey Matt, Great question! I added a section above that explains a few of the differences between SUBTOTAL and AGGREGATE. I also explain why it’s good to still know SUBTOTAL, even though AGGREGATE has more features. I will write a separate post that details the differences. Thanks!

    Reply

Leave a Reply: