Menu
EXCEL SUBTOTAL FUNCTION
Excel SUBTOTAL Function Introduction
This function will generate a subtotal in a list or database.
Description of Excel SUBTOTAL Function
Syntax:
=SUBTOTAL(function_num,ref1,[ref2],…)
Parameter list:
- function num – a number indicating which function should be used to calculate subtotals inside a list. Below are the specifics.
- ref1 – a specified range or cell reference
- ref2 – a specified range or cell reference to subtotal (optional).
Step By Step Guide of SUBTOTAL Function
Function_num details:
Function_num (includes hidden values) | Function_num (ignores hidden values) | Function_name |
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 |
Key Points
The SUBTOTAL function contains the values of hidden rows, with function num constants ranging from 1 to 11. The SUBTOTAL function excludes values of hidden rows for the function num constants 101 to 111.
No matter whatever function num value you choose, the SUBTOTAL function ignores any rows that are not included in the result of a filter. This function should be used if you have filtered any data and wish to get the SUM or AVERAGE of the filtered values since it ignores the unfiltered values. The SUBTOTAL function is intended for vertical ranges or columns of data.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
