Thursday, August 11, 2016

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.
ValueExplanation
(includes hidden values)
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP
ValueExplanation
(ignores hidden values)
101AVERAGE
102COUNT
103COUNTA
104MAX
105MIN
106PRODUCT
107STDEV
108STDEVP
109SUM
110VAR
111VARP
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:
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