# 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 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 to use the SUBTOTAL function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel 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: