totn Excel Functions

MS Excel: How to use the 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.

The SUBTOTAL function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUBTOTAL function can be entered as part of a formula in a cell of a worksheet.

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.

Returns

The SUBTOTAL function returns a numeric value.

Applies To

  • Excel for Office 365, Excel 2019, 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:

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