# SUBTOTAL Function - Work on Filtered Data in Excel

Author: | Edits: don

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.

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. 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.

## Question? Ask it in our Excel Forum

Excel Function: SUBTOTAL()