Syntax of the SUBTOTAL Function
Use the SUBTOTAL function in Excel to summarize the values in a worksheet in different ways. It is especially helpful when your worksheet contains hidden rows that you want to include in the calculation. The syntax of the SUBTOTAL function is: SUBTOTAL(function_num,ref1,ref2,…) The function_num argument is required and specifies the type of mathematical operation to use for the subtotal. The SUBTOTAL function can add numbers, calculate the average value of selected numbers, find the maximum and minimum values in a range, count the number of values in a selected range, and more. This argument is a number and depends on whether you want to include hidden rows in the result or exclude hidden rows from the result. These rows may be manually hidden or hidden by a filter. The function_num arguments include: The ref2,… arguments are optional. These are additional cells that are included in the calculation.
Use the SUBTOTAL Function with Hidden Rows
Excel functions can be entered manually or with the help of the Function Arguments dialog box. To illustrate how to enter the function manually using the formula bar, the following example uses the COUNT function_num argument to count the number of values in visible rows and in both visible and hidden rows. To use the SUBTOTAL function to count the number of rows in a worksheet:
Use the SUBTOTAL Function with Filtered Data
Using the SUBTOTAL function on filtered data ignores data in rows that have been removed by the filter. Each time the filter criteria changes, the function recalculates to show the subtotal for the visible rows. To use the SUBTOTAL function to see the differences in calculation results while filtering data:
Use the SUBTOTAL Function with Grouped Data
When data is grouped, there’s a way to apply the SUBTOTAL function to each individual group and then calculate the grand total for the entire data set.
