SUBTOTAL

The SUBTOTAL function returns subtotals for a range of numbers applying another function.

This function can be useful when working with large data sets and when using filters.

Syntax

SUBTOTAL(function_code, range1, [range2, ...])

Argument	Description	Permitted values
function_code	The numeric code of the function you want to use to calculate the subtotal. Use codes 1-11 to include manually hidden cells in the count, or 101‑111 to exclude them. Cells hidden using a filter are always excluded.	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
range1	First range or array over which to calculate a subtotal	Range of cells or array of numbers
[range2; ...]	(optional) Additional ranges or arrays over which to calculate subtotals	Range of cells or array of numbers

Sum of values

Calculating the sum of values in the A1:A10 range, ignoring hidden rows:

=SUBTOTAL(9, A1:A10)

Here, 9 corresponds to the SUM function.

Average value

Calculating the average value in the range B1:B10:

=SUBTOTAL(1, B1:B10)

Here, 1 corresponds to the AVERAGE function.

Maximum value

Search for the maximum value in the range C1:C10:

=SUBTOTAL(4, C1:C10)

Here, 4 corresponds to the MAX function.

The SUBTOTAL function ignores rows hidden by filters which makes it useful for data analysis.
Make sure you use the correct function numbers to get the statistical values you need.