# 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. #### Example 1: Sum a Range after applying Auto Filter

The below example filtered number “2” 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”.

 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