tech on the net

MS Excel: SUBTOTAL Function (WS)

Learn 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 Microsoft Excel SUBTOTAL function is:

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

Parameters or Arguments

method is 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.

method can be a value ranging from 1 - 11 as follows: (which includes 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

method can be a value ranging from 101 - 111 as follows: (which ignores hidden values in the calculation)

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 are 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 you would use the SUBTOTAL function as a worksheet function in Microsoft Excel:

Microsoft Excel

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

=SUBTOTAL(1, D2:D5) would return 22.3925
=SUBTOTAL(2, D2:D5) would return 4
=SUBTOTAL(3, D2:D5) would return 4
=SUBTOTAL(4, D2:D5) would return 35.88
=SUBTOTAL(5, D2:D5) would return 7
=SUBTOTAL(6, D2:D5) would return 136191.51
=SUBTOTAL(7, D2:D5) would return 11.91825316
=SUBTOTAL(8, D2:D5) would return 10.32151
=SUBTOTAL(9, D2:D5) would return 89.57
=SUBTOTAL(10, D2:D5) would return 142.0447583
=SUBTOTAL(11, D2:D5) would return 106.5335688