WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » SUBTOTAL Excel Function

SUBTOTAL Excel Function

SUBTOTAL Function in Excel

Excel Subtotal function is an inbuilt function which is used to give us the subtotal of a given data table or data sets, this formula takes two arguments one is the function number and the second is the range, range can be multiple, excel has preset of function number which we need to remember before using this function as 1 represents average and 2 represents count and so on.

Syntax

SUBTOTAL Formula

The SUBTOTAL formula accepts the following parameters and arguments:

  • function_num – This is the number that specifies which function should be used for the calculations of the subtotals within a list.
  • ref1– This is a named range or a reference to the subtotal.
  • ref2 – This is optional. This is the named range or reference to the subtotal.
  • Return Value: The return value will be a numeric value, which represents a specific kind of subtotal.

Usage Notes

  • The SUBTOTAL function is used to get a subtotal in a list or a database.
  • The SUBTOTAL can use different functions when subtotaling, which includes AVERAGE, COUNT, MAX, and others (all the functions are listed below).
  • The SUBTOTAL Function excludes values in the row, which is hidden by a filter, and it is the default.
  • The SUBTOTAL function is a very useful function in Microsoft Excel.

Available Functions for SUBTOTAL

As stated above, the SUBTOTAL function can use different functions when subtotaling; here is the list of available functions with including or excluding values in the hidden rows. The behavior of the SUBTOTAL function is controlled by the argument “function_num.” See the table below –

Function

function_num

Includes hidden values

function_num

Excludes hidden values

AVERAGE

1

101

COUNT

2

102

COUNTA

3

103

MAX

4

104

MIN

5

105

PRODUCT

6

106

STDEV

7

107

STDEVP

8

108

SUM

9

109

VAR

10

110

VARP

11

111

Manually Hidden Values in SUBTOTAL Function

You must have noticed that the values which are used for function_num argument are paired in the above table, for example, 1-101, 2-102, 3-103, and so on. This is in relation to how the SUBTOTAL function deals with manually hidden rows.

Let’s make it clearer.

  • When the value of function_num is between 1-11, the SUBTOTAL function will include cells which are manually hidden.
  • When the value of function_num is between 101-111, the SUBTOTAL function will exclude or ignore the values in rows that are manually hidden.

Always remember that the SUBTOTAL function excludes the values in the cells, which are hidden with a filter. Regardless of function_num, the values which are present in the ‘filtered out’ rows are excluded.

How to Open the SUBTOTAL Function in Excel?

You can download this SUBTOTAL Function Excel Template here – SUBTOTAL Function Excel Template
  1. You can simply enter the desired SUBTOTAL formula in the required cell to attain a return value on the argument.
  2. You can manually open the SUBTOTAL formula dialogue box in the spreadsheet and enter the logical values to attain a return value.
  3. Consider the screenshot below to see the SUBTOTAL option under the Math & Trig Function menu.

Step 1 to Open SUBTOTAL Function

  1. Click on the SUBTOTAL option. The SUBTOTAL formula dialogue box will open where you can put the argument values to obtain a return value.

Step 2 to Open SUBTOTAL Function

How to use the SUBTOTAL Function in Excel? (with Examples)

Let’s look below at some of the examples of the SUBTOTAL function. These examples will help you in exploring the use of the SUBTOTAL function.

SUBTOTAL Example 1

Based on the above Excel spreadsheet, let’s consider three examples and see the SUBTOTAL function return based on the SUBTOTAL Formula in Excel.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

SUBTOTAL Example 1-1

Consider the below screenshots of the above examples for clear understanding.

Example #1

SUBTOTAL Example 1-2

Example #2

SUBTOTAL Example 1-3

Example #3

SUBTOTAL Example 1-4

Example #4

SUBTOTAL Example 1-5

Example #5

SUBTOTAL Example 1-6

Example #6

SUBTOTAL Example 1-7

Example #7

Excel SUBTOTAL Function Example 1-8

Example #8

Excel SUBTOTAL Function Example 8

Example #9

Excel SUBTOTAL Function Example 9

Example #10

Excel SUBTOTAL Function Example 10

Example #11

Excel SUBTOTAL Function Example 11

Errors in SubTotal Function

If you get any kind of error from the SUBTOTAL Function in excel, then it can be any one of the following –

  • #VALUE! – This error occurs if the given function_num argument is not one of the permitted values (i.e., between 1 -11 or 101-111).
  • #DIV/0! – This error occurs of the required calculation is involving a division by numerical 0. The required calculation can be anything, for example, finding the average, standard deviation, or variance for a range of cells which are not containing any numeric value.

Things to Remember

  • The SUBTOTAL Function returns the subtotal of the numbers which are present in a column of a list or a database.
  • The SUBTOTAL function has the ability to return a SUM, AVERAGE, COUNT, MAX, and others with either including or excluding values in hidden rows.
  • When the value of function_num is between 1-11, the SUBTOTAL function will include the hidden values.
  • When the value of function_num is between 101-111, the SUBTOTAL function will exclude or ignore the hidden values.
  • In filtered lists, the SUBTOTAL will always ignore the values which are present in the hidden rows, which is regardless of function_num.
  • The SUBTOTAL Function ignores other subtotal value which already exists in references. It is done to prevent the error of double counting.
  • The Excel SUBTOTAL Function is designed for the calculation of vertical data value, which is arranged vertically in the excel sheet. In horizontal ranges, the values in the hidden columns will always be included.

SUBTOTAL Excel Function Video

Recommended Articles

This has been a guide to SUBTOTAL in Excel. Here we discuss the SUBTOTAL Formula in excel and how to use it along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • SIGN Function
  • AGGREGATE Excel Function
  • Product Excel Function | Examples
  • SUM in Excel
  • Find Links in Excel
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download SUBTOTAL Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More