Microsoft defines Excel SUBTOTAL as the function that returns a subtotal in a list or database. In this context, “subtotal” is not just totaling numbers in a defined range of cells. Unlike other Excel functions that are designed to do only one specific thing, SUBTOTAL is amazingly versatile – it can perform different arithmetic and logical operations such as counting cells, calculating average, finding the minimum or maximum value, and more.
Syntax: =SUBTOTAL(function_num,ref1,[ref2],…)
The SUBTOTAL function syntax has the following arguments:
- Function_num – a number that specifies which function to use for the subtotal.
- Ref1, Ref2, … – one or more cells or ranges to subtotal. The first ref argument is required, others (up to 254) are optional.
The function_num argument can belong to one of the following sets:
- 1 – 11 ignore filtered-out cells, but include manually hidden rows.
- 101 – 111 ignore all hidden cells – filtered out and hidden manually.
Function_num | Function | Description | |
1 | 101 | AVERAGE | Returns the average of numbers. |
2 | 102 | COUNT | Counts cells that contain numeric values. |
3 | 103 | COUNTA | Counts non-empty cells. |
4 | 104 | MAX | Returns the largest value. |
5 | 105 | MIN | Returns the smallest value. |
6 | 106 | PRODUCT | Calculates the product of cells. |
7 | 107 | STDEV | Returns the standard deviation of a population based on a sample of numbers. |
8 | 108 | STDEVP | Returns the standard deviation based on an entire population of numbers. |
9 | 109 | SUM | Adds up the numbers. |
10 | 110 | VAR | Estimates the variance of a population based on a sample of numbers. |
11 | 111 | VARP | Estimates the variance of a population based on an entire population of numbers. |
Example: Let’s look at some Excel SUBTOTAL function examples and explore how to use the SUBTOTAL function as a worksheet function in Microsoft Excel:
Syntax: =SUBTOTAL(1,D2:D12)
Result:
Based on the Excel spreadsheet above, the following SUBTOTAL examples would return:
Syntax: =SUBTOTAL(2,D2:D12)
Result: 11
Syntax: =SUBTOTAL(3,D2:D12)
Result: 11
Syntax: =SUBTOTAL(4,D2:D12)
Result: 180
Syntax: =SUBTOTAL(5,D2:D12)
Result: 30
Syntax: =SUBTOTAL(6,D2:D12)
Result: 1.0538E+21
Syntax: =SUBTOTAL(7,D2:D12)
Result: 48.10877826
Syntax: =SUBTOTAL(8,D2:D12)
Result: 45.86992028
Syntax: =SUBTOTAL(9,D2:D12)
Result: 1027
Syntax: =SUBTOTAL(10,D2:D12)
Result: 2314.454545
Syntax: =SUBTOTAL(11,D2:D12)
Result: 2104.049587
Note:
- The SUBTOTAL Function returns the subtotal of the numbers which are present in a column of a list or a database.
- The SUBTOTAL function has the ability to return a SUM, AVERAGE, COUNT, MAX, and others with either including or excluding values in hidden rows.
- When the value of function_num is between 1-11, the SUBTOTAL function will include the hidden values.
- When the value of function_num is between 101-111, the SUBTOTAL function will exclude or ignore the hidden values.
- In filtered lists, the SUBTOTAL will always ignore the values which are present in the hidden rows, which is regardless of function_num.
- The SUBTOTAL Function ignores other subtotal value which already exists in references. It is done to prevent the error of double counting.
- The Excel SUBTOTAL Function is designed for the calculation of vertical data value which is arranged vertically in the excel sheet. In horizontal ranges, the values in the hidden columns will always be included.