How to use SUBTOTAL Function (WS)

Description

The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.

Syntax

The syntax for the SUBTOTAL function in Microsoft Excel is:

SUBTOTAL( method, range1, [range2, ... range_n] )

Parameters or Arguments

method

The type of subtotal to create, but be careful which method you select. method can be a value ranging from 1 - 11 that includes hidden values or a value ranging from 101 - 111 that ignores hidden values in the calculation.

Value	Explanation (includes hidden values)
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

Value	Explanation (ignores hidden values)
101	AVERAGE
102	COUNT
103	COUNTA
104	MAX
105	MIN
106	PRODUCT
107	STDEV
108	STDEVP
109	SUM
110	VAR
111	VARP

range1, range2, ... range_n

The ranges of cells that you want to subtotal.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel SUBTOTAL function examples and explore how to use the SUBTOTAL function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUBTOTAL examples would return:

=SUBTOTAL(1, D2:D5)
Result: 22.3925

=SUBTOTAL(2, D2:D5)
Result: 4

=SUBTOTAL(3, D2:D5)
Result: 4

=SUBTOTAL(4, D2:D5)
Result: 35.88

=SUBTOTAL(5, D2:D5)
Result: 7

=SUBTOTAL(6, D2:D5)
Result: 136191.51

=SUBTOTAL(7, D2:D5)
Result: 11.91825316

=SUBTOTAL(8, D2:D5)
Result: 10.32151

=SUBTOTAL(9, D2:D5)
Result: 89.57

=SUBTOTAL(10, D2:D5)
Result: 142.0447583

=SUBTOTAL(11, D2:D5)
Result: 106.5335688