Excel Functions Tutorials
- Maths Functions in Excel
- ABS Function in Excel
- AGGREGATE Excel Function
- CEILING Excel Function
- COMBIN Excel Function
- COS Excel Function
- EVEN Function in Excel
- EXPONENTIAL Excel Function
- Excel Matrix Multiplication
- Excel Minus Formula
- FLOOR Function in Excel
- Histogram Formula
- INT Excel Function (Integer)
- LN Excel Function
- LOG Excel Function
- Mode in Excel
- ODD Function in Excel
- POWER Function in Excel
- PRODUCT Excel Function
- PRODUCT Formula in Excel
- PI in Excel
- Quotient in Excel
- Running Total in Excel
- RAND Excel Function
- Rand Excel Formula
- RANDBETWEEN in Excel
- MROUND in Excel
- ROUND in Excel
- ROUND Formula in Excel
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- SIGN Excel Function
- SIN Excel Function
- Square Root in Excel (SQRT)
- SUBTOTAL Excel Function
- SUM Function in Excel
- SUM Formula in Excel
- Excel SUM Shortcut
- SUMIF in Excel
- SUMIFS in Excel
- SUMIF Not Blank
- SUMIFS with Dates
- SUMIF Between Two Dates
- Sumif Text in Excel
- SumIf with Multiple Criteria
- SUMPRODUCT Function in Excel
- SUMPRODUCT Formula in Excel
- SUMPRODUCT with Multiple Criteria
- How to Sum Multiple Rows in Excel?
- TAN Excel Function
- TANH in Excel
- Average vs Weighted Average
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
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.
- 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 –
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?
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.
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
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 –