How To Use Excel SUBTOTAL Function

This post will guide you how to use Excel SUBTOTAL function with syntax and examples in Microsoft excel.

Table of Contents

Description

The Excel SUBTOTAL function returns the subtotal of the numbers in a list or database.

The SUBTOTAL function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The SUBTOTAL function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the SUBTOTAL function is as below:

= SUBTOTAL (function_num, ref1, [ref2])

Where the SUBTOTAL function argument is:
Function_num – This is a required argument.  It can be set as 1-11 or 101-111 for the subtotal.  1-11 that includes hidden rows and 101-111 excludes hidden rows.

Function_num
(includes hidden values)
Function_num
(ignores hidden values)
Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

 

Ref1– This is a required argument. The first named range or reference that you want to subtotal.

Example

the below examples will show you how to use Excel SUBTOTAL function to return the subtotal of the numbers in a list.

#1 =SUBTOTAL(103,B:B)

excel subtotal function example1

Note: the above formula will call COUNTA function to count the number of cells(B:B) that contain numbers. It will return value 5.

#2 =SUBTOTAL(109,B:B)

excel subtotal function example1

Note: the above excel formula will call SUM function to add all numbers in range cell B:B, so it will return value: 160.

 

Leave a Reply