Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to combine 2 or more chart types in a single chart in Excel
  • Reverse List in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Excel Pie Chart
  • How To Remove Duplicates In Excel Column Or Row?

References

  • Count rows with at least n matching values
  • Extract all partial matches in Excel
  • Get nth match with INDEX / MATCH in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Count rows that contain specific values in Excel

Data Validations

  • Excel Data validation only dates between
  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Excel Data validation date in specific year

SUBTOTAL function: Description, Usage, Syntax, Examples and Explanation

by

What is SUBTOTAL function in Excel?

SUBTOTAL function is one of the Math and Trig functions in Microsoft Excel that returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax of SUBTOTAL function

SUBTOTAL(function_num,ref1,[ref2],…)

The SUBTOTAL function syntax has the following arguments:

  • Function_num: The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
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: The first named range or reference for which you want the subtotal.
  • Ref2,..(Optional): Named ranges or references 2 to 254 for which you want the subtotal.

SUBTOTAL formula explanation

  • The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
  • If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
  • If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
  • For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cellsgroup on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
  • The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

Example of SUBTOTAL function

Steps to follow:

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Data
120
10
150
23
Formula Description Result
=SUBTOTAL(9,A2:A5) The sum of the subtotal of the cells A2:A5, using 9 as the first argument. 303
=SUBTOTAL(1,A2:A5) The average of the subtotal of the cells A2:A5, using 1 as the first argument. 75.75
Notes
The SUBTOTAL function always requires a numeric argument (1 through 11, 101 through 111) as its first argument. This numeric argument is applied to the subtotal of the values (cell ranges, named ranges) that are specified as the arguments that follow.

Post navigation

Previous Post:

TRUE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Pie Chart

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • OR function Examples in Excel

Date Time

  • Get last weekday in month in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate workdays per month in Excel
  • Count dates in current month in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to generate random number weighted probability in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to count total columns in range in Excel
  • Excel Default Templates
  • How to calculate project complete percentage in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning