Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
SUBTOTAL on Excel (Table of Contents)
- SUBTOTAL in Excel
- SUBTOTAL Formula in Excel
- SUBTOTAL Function in Excel – Usage Notes
- Available Functions for SUBTOTAL
- Manually Hidden Values in SUBTOTAL Function in Excel
- How to Open the SUBTOTAL Function in Excel?
- How to Use SUBTOTAL Function in Excel
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 in Excel 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.
SUBTOTAL Function in Excel – Usage Notes
- The SUBTOTAL Excel Function is used to get a subtotal in a list or a database.
- The SUBTOTAL in Excel can use different functions when subtotaling, which includes AVERAGE, COUNT, MAX and others (all the functions are listed below).
- The SUBTOTAL in Excel 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 the Microsoft Excel.
Available Functions for SUBTOTAL
As stated above, the SUBTOTAL function in Excel 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 –
Includes hidden values
Excludes hidden values
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?
- You can simply enter the desired SUBTOTAL formula excel in the required cell to attain a return value on the argument.
- You can manually open the SUBTOTAL formula in Excel 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.
SUBTOTAL in Excel Example #1
SUBTOTAL in Excel Example #2
SUBTOTAL in Excel Example #3
SUBTOTAL in Excel Example #4
SUBTOTAL in Excel Example #5
SUBTOTAL in Excel Example #6
SUBTOTAL in Excel Example #7
SUBTOTAL in Excel Example #8
SUBTOTAL in Excel Example #9
SUBTOTAL in Excel Example #10
SUBTOTAL in Excel 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 About the SUBTOTAL in Excel
- 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.
You can download this SUBTOTAL in Excel template here – SUBTOTAL Function Excel Template
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 –