SUBTOTAL Function - Work on Filtered Data in Excel
Perform basic functions on a filtered dataset in excel, including SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, and more.
The SUBTOTAL() function is used like a conduit function that allows other functions to work through it and on only the visible rows after you filter data and also to ignore any values from manually hidden rows.
Sections:
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
| Argument | Description |
|---|---|
| Function_num |
A number that tells this function which function you want to use on the filtered data. Each number corresponds to a different function. Each function and number is outlined in the next table. Each function has 2 numbers, one that tells the function to work on filtered and unfiltered data and one that tells the function to also ignore data that is hidden manually. |
| Ref1 |
The range on which you want to run the function. |
| [Ref2] | Optional. If there are multiple ranges, you can keep adding them. You can have up to 254 references. |
[] means argument is optional.
| Function |
Function_num (works on regular and filtered data) |
Function_num (works on regular and filtered data and also ignores manually hidden rows) |
|---|---|---|
|
AVERAGE |
1 |
101 |
|
COUNT |
2 |
102 |
|
COUNTA |
3 |
103 |
|
MAX |
4 |
104 |
|
MIN |
5 |
105 |
|
PRODUCT |
6 |
106 |
|
STDEV |
7 |
107 |
|
STDEVP |
8 |
108 |
|
SUM |
9 |
109 |
|
VAR |
10 |
110 |
|
VARP |
11 |
111 |
This last table lists the possible values for the function_num argument in the SUBTOTAL function. You can use the numbers from the middle column or the right column.
Numbers from the middle column tell the SUBTOTAL function to work on regular data or filtered data while at the same time including any values that were manually hidden, such as when you right-click a row and then click Hide.
Numbers from the right column tell the SUBTOTAL function to ignore any manually hidden rows and to still work on regular and filtered data.
Example
Let's sum the values from a filtered list.
=SUBTOTAL(9,B4:B8)
9 comes from the function_num list above and tells the SUBTOTAL function to perform a SUM on the data.
Result:
This works when the data is filtered and also when it is unfiltered just the same.
Hidden Rows
To perform the SUM on data that may have manually hidden rows, not just filtered data, and where you don't want those hidden rows to be included in the sum, use the number from the far right column in the function_num table above.
As such, 9 becomes 109
=SUBTOTAL(109,B4:B8)
To test this function in Excel, just manually hide (right-click the row number > Hide) one of the rows in the range to sum.
Notes
If you are going to ever filter data or hide data in your worksheet and want to have functions that work on only the visible data, you need to use the SUBTOTAL function.
No matter which function_num you use, either from the right or middle column of the table above, they both work the same on filtered data, which is that they exclude data that does not appear when you apply a filter; the only difference is how they treat manually hidden rows of data.
This function works with rows of data and not columns of data, which shouldn't affect your data sets much, to be honest.
Make sure to download the sample file for this tutorial so you can work with these examples in Excel.
Question? Ask it in our Excel Forum
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)