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:
Start with a worksheet that contains multiple rows of data. Select the cell that will contain the count of the visible rows. In the function bar, enter =SUBTOTAL. As you type, Excel suggests a function. Double-click the SUBTOTAL function. To use the Function Arguments dialog box to enter the SUBTOTAL function, go to Formulas and select Math & Trig > SUBTOTAL. In the drop-down menu that appears, double-click the 102 – COUNT function_num argument. Type a comma (,). In the worksheet, select the cells to include in the formula. Press Enter to see the result in the cell you selected in step 2. Select the cell that will contain the count of the visible and hidden rows. In the function bar, enter =SUBTOTAL. As you type, Excel suggests a function. Double-click the SUBTOTAL function. In the drop-down menu that appears, double-click the 2 – COUNT function_num argument, then type a comma (,). In the worksheet, select the cells to include in the formula, then press Enter. Hide several rows of data. In this example, rows with only sales below $100,000 were hidden.
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:
Create SUBTOTAL formulas. For example, create formulas to determine the subtotal and average values of the filtered data. It doesn’t matter if you use the function_num argument for visible or hidden rows. Both arguments provide the same result in filtered data. Select any cell in the data set. Go to Home, then select Sort & Filter > Filter. Use the drop-down arrows to filter the worksheet data. Notice how the values change each time you select different filter criteria.
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.
Select any cell in the data set. Select Data > Subtotal to open the Subtotal dialog box. Select the At each change in drop-down arrow and choose the grouping to which each subtotal will be calculated. Select the Use function drop-down arrow and choose a function_num. In the Add subtotal to list, select the column that the formula will be applied. Select OK. Subtotals are inserted for each data group, and a grand total is inserted at the bottom of the data set. To change the function_num, highlight any cell in the data set and select Data > Subtotal. Then, make your choices in the Subtotal dialog box.
