Table of Contents
About
SUBTOTAL function in Excel can return different aggregations like SUM, COUNT, AVERAGE, MAX, MIN etc. for the supplied values. SUBTOTAL function also has the option to include or exclude hidden rows.
Function Type
Math and trigonometry
Purpose
Get the subtotal in a list or database
Return value
A number equivalent to the preferred aggregation.
Syntax
=SUBTOTAL(function_num,ref1,[ref2],...)
Arguments
function_num – A number from 1-11 or 101-111 that specifies the function to use. 1-11 includes manually-hidden rows, while 101-111 excludes them. Filtered-out cells are always ignored.
| Function | function_num to include hidden Rows | function_num to exclude 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 |
ref1 – A reference or named range to subtotal
ref2 – [optional] The second reference or second named range to subtotal
Example 1
Notes
SUBTOTAL function ignores formulas containing SUBTOTAL function.
Total Row feature of Excel Tables uses SUBTOTAL function to provide aggregations.
Excel Functions in Alphabetical Order (Complete list)
Complete List of Excel Functions (Category wise)