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