How To Use Excel SUBTOTAL Function

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

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.

 

Related Posts

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Only Sum Visible Cells/Rows in a Filtered List in Excel
How to Only Sum Visible CellsRows in a Filtered List9

Sometimes we may want to only sum the visible cells in a filtered list or a table. We may notice that if we use SUM function directly with selecting the visible reference in the filtered list, all data in the ...

How to Sum Only Filtered Data or Visible Cell Values in Excel
sum only filtered data3

This post will guide you how to sum only visible cell values in Excel. How do I sum only visible cell values in a selected range with a formula in Excel. How to sum only visible rows when filtering data ...

How to Count And Sum Cells by Color in Excel
count and sum cells by color10

This post will guide you how to count and sum cells by font color or cell color in Excel. How do I sum cell values by font color with VBA code in Excel. How to sum cells by color with ...

Count Blank or Non-blank Cells in Filtered Range
count blank cells3

This post will guide you how to count blank or empty cells in a filtered range of cells in excel. How do I count non-blank cells in a filtered range of cells in excel. How to count blank or non-blank ...

Excel DPRODUCT Function
excel dproduct examples1

This post will guide you how to use Excel DPRODUCT function with syntax and examples in Microsoft excel. Description The Excel DPRODUCT returns the product of values from a set of records that match criteria that you specify. The DPRODUCT ...

Excel XIRR Function
excel xirr examples1

This post will guide you how to use Excel XIRR function with syntax and examples in Microsoft excel. Description The Excel XIRR function returns the internal rate of return for a series of cash flows that is not necessarily periodic. ...

Excel VDB Function
excel vdb examples1

This post will guide you how to use Excel VDB function with syntax and examples in Microsoft excel. Description The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. ...

Excel SYD Function
excel syd examples1

This post will guide you how to use Excel SYD function with syntax and examples in Microsoft excel. Description The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric ...

Excel SLN Function
excel sln examples1

This post will guide you how to use Excel SLN function with syntax and examples in Microsoft excel. Description The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it ...

Sidebar