SUBTOTAL
The SUBTOTAL function in Excel is a versatile tool that performs calculations on a range of data while allowing the user to specify the type of operation to apply, such as sum, average, count, maximum, minimum, etc. This function is useful for generating subtotal results within filtered data sets or ranges.
Syntax
=SUBTOTAL(function_num, ref1, [ref2, ...]) Arguments
| Argument | Required | Description |
|---|---|---|
| function_num | Yes | A number that specifies which function to use for the calculation. It can range from 1 to 11, or 101 to 111 for functions that include hidden values in the calculation. |
| ref1 | Yes | The first reference for which you want to apply the subtotal function. |
| ref2 | Yes | ..., Additional references for which you want to apply the subtotal function. |
About
When you're dealing with lists or tables in Excel and need to perform calculations on specific subsets of data, SUBTOTAL comes to the rescue. This function enables you to carry out various calculations, such as sum, average, count, maximum, or minimum, within a designated range of values. It's particularly handy when working with filtered data or logically structured sets where you want to exclude hidden rows from the calculations. SUBTOTAL ensures accurate and dynamic results by accounting for only visible rows to derive the subtotal figures. By specifying a function_num corresponding to the desired operation, you can swiftly obtain subtotal values that cater to your analytical requirements. Whether you're summarizing data or analyzing discrete sections, SUBTOTAL proves itself as a go-to function for efficient data processing in Excel.
Examples
Suppose you have a list of sales figures in cells A2:A10, and you want to calculate the total sum of visible values after applying a filter. The SUBTOTAL formula for summing the visible values would be: =SUBTOTAL(9, A2:A10)
Consider you have a column (B) containing various expense categories and a corresponding column (C) with the amounts. You want to calculate the average of the visible amounts based on certain criteria. The SUBTOTAL formula for averaging the visible values would be: =SUBTOTAL(1, C2:C100)
If you have multiple regions with sales data and wish to find the maximum value per region after filtering, you can use the SUBTOTAL function with the MAX function. For instance, to determine the maximum of visible values in column D for region ‘East’, you can enter: =SUBTOTAL(104, D2:D500)
Consider you have a column (B) containing various expense categories and a corresponding column (C) with the amounts. You want to calculate the average of the visible amounts based on certain criteria. The SUBTOTAL formula for averaging the visible values would be: =SUBTOTAL(1, C2:C100)
If you have multiple regions with sales data and wish to find the maximum value per region after filtering, you can use the SUBTOTAL function with the MAX function. For instance, to determine the maximum of visible values in column D for region ‘East’, you can enter: =SUBTOTAL(104, D2:D500)
Tips & notes
When using the SUBTOTAL function, ensure that the function_num aligns with the desired calculation. The function_num values differ depending on whether you want to include hidden values in the calculation or not. It's crucial to understand the function_num options for accurate subtotal computations based on your data arrangement and filtering scenarios.
Common questions
How does the SUBTOTAL function handle hidden rows in Excel?
The SUBTOTAL function excludes hidden rows from the calculation, ensuring that only visible rows are considered in the specified operation. This feature allows for accurate subtotal results when working with filtered datasets.
Can you use multiple references in the SUBTOTAL function?
Yes, the SUBTOTAL function allows for multiple references (ref1, ref2, ...) to be included in the calculation. You can apply the same subtotal operation to several ranges of data for comprehensive analysis.
What is the purpose of the function_num parameter in the SUBTOTAL function?
The function_num parameter in the SUBTOTAL function determines which operation to perform on the specified data range. It ranges from 1 to 11 for standard functions and extends to 101 to 111 for functions that include hidden values in the calculation.