SUBTOTAL

Returns SUM, AVERAGE, STDEV, etc. results for filtered data.

Syntax:

SUBTOTAL(function; range)

range is the overall range from which cells for calculation are selected by filtering.

function is a number that specifies the function to calculate, as follows:

function	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STEDEVP
9	SUM
10	VAR
11	VARP

If range contains other SUBTOTAL functions they are ignored to avoid double counting.

Example:

You have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter so that you only see rows containing the city Hamburg. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows.

SUBTOTAL(9; B1:B5)

returns the desired result.

City	Value
Hamburg	20
Hamburg	15
Hamburg	5


SUBTOTAL(9, B1:B5)	40

Application:

Imagine you are a sales manager for a company that sells office supplies. You have a spreadsheet tracking all your sales, and you want to analyze the data based on different criteria, such as sales representatives or regions.

Sales Data Table

	Sales Rep	Region	Product	Units Sold	Total Revenue
	A	B	C	D	E
1	Alice	East	Notebooks	50	$250.00
2	Bob	West	Pens	100	$100.00
3	Alice	East	Paper	200	$400.00
4	Charlie	Central	Staplers	75	$375.00
5	Bob	West	Folders	150	$300.00
6	Alice	East	Notebooks	120	$600.00
7	Charlie	Central	Pens	80	$80.00

Now, let's use the SUBTOTAL function to analyze this data.

First, you would add a row below the table to calculate the total revenue. In cell E8, you would enter the following formula:

SUBTOTAL(9, E1:E7)

Let's break down this formula:

9: This is the function_num argument. The number 9 corresponds to the SUM function. Other numbers can be used for different functions (e.g., 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, etc.).
E1:E7: This is the ref1 argument, which specifies the range of cells you want to perform the calculation on.

Initially, this formula will return the sum of all the values in the "Total Revenue" column, which is $2,105.

Now, let's see how the SUBTOTAL function works when you apply a filter.

Scenario: You want to see the total revenue for the 'East' region only.

Apply a filter to your data table.
In the "Region" column, select the filter dropdown and uncheck all regions except for "East".

Your table will now look like this:

	Sales Rep	Region	Product	Units Sold	Total Revenue
	A	B	C	D	E
1	Alice	East	Notebooks	50	$250.00
2	Alice	East	Paper	200	$400.00
3	Alice	East	Notebooks	120	$600.00

The SUBTOTAL function in cell E8 will automatically update to reflect the filtered data. The value in cell E8 will now be $1,250, which is the sum of the revenue for the three rows displayed.

If you had used the regular SUM function (SUM(E1:E7)) instead of SUBTOTAL, the value would have remained $2,105, ignoring the filter. This is the key difference and why SUBTOTAL is the preferred choice for working with filtered datasets.