How to Use the Excel Subtotal Function

Include or exclude hidden values in hidden rows and filtered data

When your Excel worksheet contains hidden rows, filtered data, or grouped data, use the Excel SUBTOTAL function. The SUBTOTAL function can include or exclude hidden values in calculations. In addition to finding the total of a data group, Excel can calculate the average, maximum, minimum, standard deviation, and variance of your data. Here’s how to insert subtotals in Excel.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, and Excel 2016.

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.

The SUBTOTAL function ignores cells that don’t contain data and cells with non-numeric values.

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:

Function Task function_num function_num
  (includes hidden values) (excludes hidden values)
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

The function_num reference arguments 1 through 11 only include values in hidden rows when using the Hide command to hide rows. When using the Filter command, SUBTOTAL calculations do not include hidden filter results.

The ref1 argument is required. These are the cells used to calculate the results of the selected function_num argument. This argument can be a value, a single cell, or a range of cells.

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:

  1. Start with a worksheet that contains multiple rows of data.

  2. Select the cell that will contain the count of the visible rows.

  3. 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.

    Enter t he SUBTOTAL function in the Excel function bar
  4. In the drop-down menu that appears, double-click the 102 – COUNT function_num argument.

    Use the COUNT function_num argument with the SUBTOTAL function
  5. Type a comma (,).

    Complete the function_num argument in the SUBTOTAL function in Excel
  6. In the worksheet, select the cells to include in the formula.

    Add the cells to include in the formula as the ref1 argument of the SUBTOTAL function
  7. Press Enter to see the result in the cell you selected in step 2.

    The complete SUBTOTAL function using the COUNT argument
  8. Select the cell that will contain the count of the visible and hidden rows.

  9. In the function bar, enter =SUBTOTAL. As you type, Excel suggests a function. Double-click the SUBTOTAL function.

  10. In the drop-down menu that appears, double-click the 2 – COUNT function_num argument, then type a comma (,).

  11. In the worksheet, select the cells to include in the formula, then press Enter.

    Use the SUBTOTAL function to count visible and hidden rows
  12. Hide several rows of data. In this example, rows with only sales below $100,000 were hidden.

    The completed SUBTOTAL function to count visible and hidden values

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:

  1. 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.

    Use the SUBTOTAL function on filtered data
  2. Select any cell in the data set.

  3. Go to Home, then select Sort & Filter > Filter.

    Filter data in an Excel worksheet to see the result of the SUBTOTAL function
  4. Use the drop-down arrows to filter the worksheet data.

    How to filter data in an Excel worksheet
  5. Notice how the values change each time you select different filter criteria.

    Results of the SUBTOTAL function on filtered data in an Excel worksheet

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.

  1. Select any cell in the data set.

  2. Select Data > Subtotal to open the Subtotal dialog box.

    Use the Subtotal command to create subtotals and grand totals for grouped data in Excel
  3. Select the At each change in drop-down arrow and choose the grouping to which each subtotal will be calculated.

  4. Select the Use function drop-down arrow and choose a function_num.

  5. In the Add subtotal to list, select the column that the formula will be applied.

  6. Select OK.

    Set the criteria for the Subtotal comman in an Excel worksheet
  7. Subtotals are inserted for each data group, and a grand total is inserted at the bottom of the data set.

    Subtotal and a grand total inserted in grouped data in an Excel worksheet
  8. 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.