SUBTOTAL Function - Work on Filtered Data in Excel

Add to Favorites

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

Example

Notes

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.

29bf445b5f630e3ce7969483bacc3b2b.png

Result:

462e8fef8a116a55077d30eccd70d3c3.png

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.


Excel Function: SUBTOTAL()
Downloadable Files: Excel File