Subtotal function is an inbuilt function in excel which is used to give us the subtotal of a given data table or data sets, this function takes two arguments one is the function number and the second is the range, range can be multiple, excel has presets of function number which we need to remember before using this function as 1 represents average and 2 represents count and so on.
SUBTOTAL on Excel (Table of Contents)
- SUBTOTAL in Excel
- SUBTOTAL Formula
- Usage Notes
- Available Functions for SUBTOTAL
- Manually Hidden Values in SUBTOTAL Function
- How to Open the SUBTOTAL Function?
- How to Use SUBTOTAL Function?
SUBTOTAL Function in Excel
The Microsoft Excel SUBTOTAL function is a function which is responsible for returning the SUBTOTAL or the aggregate result of all the numbers which are present in a column of a list of particular database. The SUBTOTAL function is a built-in worksheet function for Microsoft Excel and is categorized as a Math & Trig Function. SUBTOTAL Excel Function can be entered in a cell of the worksheet as a part of the SUBTOTAL formula in Excel. The return value of the SUBTOTAL function can be a result of combined functions such as SUM, AVERAGE, COUNT, MAX and others with either including or excluding values in hidden rows.
SUBTOTAL Formula in Excel
The SUBTOTAL formula in Excel accepts the following parameters and arguments:
function_num – This is the number which 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 subtotal.
ref2 – This is optional. This is the named range or reference to 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 in Excel
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 with how 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 always 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?
Below are some of the examples of SUBTOTAL Function in Excel.
- You can simply enter the desired SUBTOTAL formula in the required cell to attain a return value on the argument.
- You can manually open the SUBTOTAL formula dialogue box in the spreadsheet and enter the logical values to attain a return value.
- Consider the screenshot below to see the SUBTOTAL option under the Math & Trig Function menu.
- Click on the SUBTOTAL option. The SUBTOTAL formula in Excel dialogue box will open where you can put the argument values to obtain a return value.
How to Use SUBTOTAL Function in Excel
Let’s look below at some of the examples of SUBTOTAL function in Excel. These examples will help you in exploring the use of the SUBTOTAL function in Excel.
Based on the above Excel spreadsheet, let’s consider three examples and see the SUBTOTAL function return based on the SUBTOTAL Formula in Excel.
Consider the below screenshots of the above examples for clear understanding.
Example #1
Example #2
Example #3
Example #4
Example #5
Example #6
Example #7
Example #8
Example #9
Example #10
Example #11
SUBTOTAL Function Errors
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 SUBTOTAL Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Excel Data Filters using VBA
- Combine in Excel
- ROW Function
- ISERROR Function
- MAX Function in Excel
- ROUND Excel Function
- SIGN Excel Function
- AGGREGATE Excel Function
- Product Excel Function
- SUM Function in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
