Excel Subtotal Function to calculate Auto Filter value

Excel Subtotal Function to calculate Auto Filter value

Excel Subtotal Function is a collection of aggregate function (Sum, Average, Min, etc), in which you can specify in argument which aggregate function to use.

There are two major differences between Subtotal Function and normal aggregate Functions

1. Subtotal Function can calculate base on filtered value in Auto Filter, while normal aggregate Function calculates all values in a Range regardless of the criteria in Filter.

2. Subtotal Function can specify whether to ignore hidden values (hidden row only, not hidden column), while normal aggregate Function calculates hidden values.

I will illustrate the differences in the examples.

Syntax of Excel Subtotal Function

SUBTOTAL(function_num, ref1, ref2, ...)
Function_num A number that represents the aggregate Function (see below)

Function_num
(include hidden values)
Function_num
(ignore hidden row values)
Function Descriptin
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
ref1, ref2 The Range to be used in the aggregate Function, maximum 254 references

Example of Excel Subtotal Function

In the following examples, I will use the below data as a source. The total of the numbers is 15.

excel_subtotal

Example 1: Sum a Range after applying Auto Filter

The below example filtered number “2”

excel_subtotal_02

Formula Result Explanation
=SUBTOTAL(9,A2:A6) 13 Subtotal Function sums filtered value
=SUM(A2:A6) 15 Sum Function sums all value

Example 2: Sum a Range with hidden row

The below example hides row 3 (number “2”), You can either hide a row by Outline or “Hide”.

excel_subtotal_03                  excel_subtotal_04

Formula Result Explanation
=SUBTOTAL(9,A2:A6) 15 Argument 9 sums all value include hidden
=SUBTOTAL(109,A2:A6) 13 Argument 109 does not sum hidden value
=SUM(A2:A6) 15 Sum Function sums all value include hidden

Note that you cannot ignore hidden column value with Function_num 101-111

Outbound References

https://support.office.com/en-US/article/SUBTOTAL-function-e27c301c-be9a-458b-9d12-b9a2ce3c62af

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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