Subtotal Function | Excel Hotshot

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.

The biggest advantage of using this function is, it results the outcome of filtered data only while applying auto filter.

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.

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:

 

 

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

=SUBTOTAL(1, D2:D10)

Result: 5,138.89

 

=SUBTOTAL(2, D2:D10)

Result: 9

 

=SUBTOTAL(3, D2:D10)

Result: 9

 

=SUBTOTAL(4, D2:D10)

Result: 9000

 

=SUBTOTAL(5, D2:D10)

Result: 1500

 

=SUBTOTAL(6, D2:D10)

Result: 53,02,33,59,37,50,00,00,00,00,00,00,00,00,00,000

 

=SUBTOTAL(7, D2:D10)

Result: 2,955.69

 

=SUBTOTAL(8, D2:D10)

Result: 2,786.65

 

=SUBTOTAL(9, D2:D10)

Result: 46,250

 

=SUBTOTAL(10, D2:D10)

Result: 87,36,111.11

 

=SUBTOTAL(11, D2:D10)

Result: 77,65,432.10