excel functions

MS Excel: SUBTOTAL Function (WS)

This Excel tutorial explains how to use the Excel SUBTOTAL function with syntax and examples.

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

The SUBTOTAL function can be used in the following versions of Microsoft Excel:

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

Type of Excel Function

The SUBTOTAL function can be used in Microsoft Excel as the following 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:

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
Share: