SUBTOTAL function

1 year ago

3 minutes

The SUBTOTAL function in Excel calculates a subtotal for a range of data, allowing you to apply different types of aggregation (like sum, average, count, etc.) to a filtered or unfiltered data set. It’s particularly useful when you have a list or table and want to perform calculations on the visible (filtered) rows only.

Syntax

=SUBTOTAL(function_num, range1, [range2], ...)

Parameters

function_num (required): A number that specifies the type of aggregation to apply. There are different numbers for different operations (e.g., sum, average, count).
range1 (required): The first range of data on which to perform the aggregation.
[range2, …] (optional): Additional ranges to perform the aggregation on. You can specify more than one range if needed.

Function Numbers (function_num)

The function_num argument determines which calculation to apply. The numbers range from 1 to 11 for operations that include hidden data, and 101 to 111 for operations that exclude hidden data (useful when data is filtered).

Number	Operation	Description
1	AVERAGE	Returns the average of the data
2	COUNT	Counts the number of cells that contain numbers
3	COUNTA	Counts the number of non-empty cells
4	MAX	Returns the largest number in the range
5	MIN	Returns the smallest number in the range
6	PRODUCT	Multiplies all the numbers together
7	STDEV	Estimates the standard deviation based on a sample
8	STDEVP	Calculates the standard deviation based on the entire population
9	SUM	Adds up the numbers in the range
10	VAR	Estimates the variance based on a sample
11	VARP	Calculates the variance based on the entire population
101-111	Same as above, but excludes hidden cells	Excludes hidden rows from the calculation if data is filtered

Key Features

Visibility-based Calculations: The SUBTOTAL function can exclude hidden rows from the calculation when filtering data, depending on the function_num.
Dynamic Updates: It recalculates automatically when data is filtered or hidden/unhidden.

Examples

Calculate the sum of a range (A1:A10), including hidden rows:
```
=SUBTOTAL(9, A1:A10)
```
Result: Sums all values in range A1:A10, including hidden rows.
Calculate the sum of a range (A1:A10), excluding hidden rows (useful after filtering):
```
=SUBTOTAL(109, A1:A10)
```
Result: Sums only the visible rows in range A1:A10 after applying a filter.
Calculate the average of a range (B1:B10), including hidden rows:
```
=SUBTOTAL(1, B1:B10)
```
Result: Averages all values in the range, including hidden rows.
Count the number of cells with numbers in a range (C1:C10), excluding hidden rows:
```
=SUBTOTAL(102, C1:C10)
```
Result: Counts only the visible cells containing numbers in the range C1:C10 after applying a filter.
Calculate the maximum value in a range (D1:D10), excluding hidden rows:
```
=SUBTOTAL(104, D1:D10)
```
Result: Returns the largest value from visible cells in range D1:D10 after applying a filter.

Notes

Hidden Rows: If you have hidden rows (either manually or via filtering), the behavior of SUBTOTAL depends on the function number. Numbers 1-11 will include hidden rows, while 101-111 will exclude them.
Dynamic Calculation: When you apply filters, SUBTOTAL will automatically update based on the visible data.
Multiple Ranges: You can use multiple ranges with the SUBTOTAL function, but each additional range must be specified in the same way as the first.

Related Functions

SUM: For basic summation, but does not handle filtering.
AVERAGE: For basic averaging, but does not handle filtering.
COUNT: For counting numbers, but does not handle filtering.
FILTER: Used for more complex filtering tasks that may work with SUBTOTAL for visible data calculations.

The SUBTOTAL function is essential for managing large datasets, especially when working with filtered or dynamic tables, and helps you focus on just the visible data without manually excluding hidden rows.

Post Views: 6