Excel SUBTOTAL for Columns: Formulas, Filters, and Tips

Formulas & functions · Updated November 18, 2026

To subtotal a column in Excel, enter a formula such as =SUBTOTAL(9,B2:B20) in a cell below the values; it adds the numbers in B2:B20. Unlike SUM, SUBTOTAL can recalculate to include only rows shown by a filter, making it useful for lists and reports that readers need to filter.

What SUBTOTAL means in Excel

SUBTOTAL is an Excel function that calculates a summary—such as a sum, average, or count—for one or more ranges. It is commonly placed at the bottom of a column of figures, but its key distinction from a basic total is how it treats filtered and hidden rows. When a filter hides records, SUBTOTAL excludes them from its result. That lets a total reflect the records currently visible without changing the formula.

The word subtotal can also refer to grouped summary lines in a report. Excel has a separate Data Subtotal command that inserts those lines and outline controls. The command uses SUBTOTAL formulas, but it is not the same thing as typing one formula yourself. The function is usually the better choice for a simple column total or a report that should respond to filters.

SUBTOTAL is most often used with a vertical range, such as the values in a sales column. It can also take more than one reference. For example, you can pass two ranges to one formula to combine their values, though if you want a separate subtotal for each column, use a separate formula for each one.

Syntax and function numbers

The function has this structure: SUBTOTAL(function_num, ref1, [ref2], ...). The function number tells Excel what calculation to perform, while ref1 and any optional references identify the cells to include. In Microsoft 365 for Windows, use commas between arguments in the formula bar. The bracketed ref2 in the syntax description is optional; do not type the square brackets into a formula.

For each calculation, there are two sets of function numbers. Numbers 1 through 11 include rows hidden manually with the Hide command, but they still exclude rows removed from view by a filter. Numbers 101 through 111 exclude both filtered-out rows and rows hidden manually. The second set adds 100 to the corresponding calculation number.

  • 1 or 101: AVERAGE
  • 2 or 102: COUNT, for numeric values
  • 3 or 103: COUNTA, for nonblank values
  • 4 or 104: MAX
  • 5 or 105: MIN
  • 6 or 106: PRODUCT
  • 7 or 107: STDEV.S
  • 8 or 108: STDEV.P
  • 9 or 109: SUM
  • 10 or 110: VAR.S
  • 11 or 111: VAR.P

For a typical total, 9 is a common choice. Use 109 instead if manually hidden rows should be left out as well. These numbers do not change the type of calculation; they change how manually hidden rows are treated.

Example: total a column of values

Suppose a worksheet has product names in A2:A6 and sales amounts in B2:B6: 120, 80, 150, 50, and 100. With the amounts in B2:B6 and the result placed in B7, this formula returns 500:

=SUBTOTAL(9,B2:B6)

The arithmetic is 120 + 80 + 150 + 50 + 100 = 500. If you filter the list so that only some records remain visible, the result changes to the sum of those visible amounts. For example, if the visible amounts are 120, 150, and 100, the subtotal is 370. The formula itself does not need to be edited when the filter changes.

If the same column contains manually hidden rows and you want those omitted too, use function number 109. With the same values in B2:B6, =SUBTOTAL(109,B2:B6) returns 500 while all five rows are visible; if the row containing 80 is manually hidden, the result becomes 420. If you want manually hidden rows included, use 9 instead. Filtering still excludes filtered-out rows with either number.

How filters affect the result

SUBTOTAL is especially useful with a filtered list. For instance, a sales table might include a Region column and an Amount column. A filter can show only the West region, and a SUBTOTAL formula referring to the Amount cells will then total the visible West records. Clear the filter or select another region and Excel recalculates the result for the newly visible records.

To add a filter in Excel for Microsoft 365 on Windows, select a cell in the data range and choose Data > Filter. Use the dropdown in the relevant column header to select the records to display. Keep the subtotal formula outside the data rows when possible, so it is clear that it is a summary rather than a record. A filter hides rows from view; it does not delete them.

A frequent misconception is that SUBTOTAL ignores every kind of hidden content. The distinction matters: filtering is always respected, while manually hidden rows are included with function numbers 1–11 and excluded with 101–111. Hidden columns are a different issue, and SUBTOTAL is designed primarily for vertical lists of rows. If you are totaling cells horizontally across a row, a SUM formula is usually clearer.

Using the Data Subtotal command

If you need a subtotal after each group—such as a sales total for every region—the Data Subtotal command can insert group summaries and outline controls. On Excel for Microsoft 365 for Windows, sort the records by the column you plan to group, select a cell in the list, then choose Data > Outline > Subtotal. In the dialog, set the grouping column under At each change in, choose the calculation under Use function, and select the column or columns to summarize under Add subtotal to.

Sorting first is important: the command detects a new group when the selected grouping value changes as it moves down the list. For example, if you want one subtotal per region, sort the records by Region before running the command. Excel inserts summary rows and outline controls that let you expand or collapse detail. To remove the inserted summaries, use the same Subtotal dialog and choose Remove All.

This is a different workflow from writing a SUBTOTAL formula in a cell. The Data Subtotal command is intended to restructure a plain range into grouped sections; a formula is more flexible when the source data is already arranged as a table or when you only need one total. Excel does not allow the Data Subtotal command to be applied directly to an Excel table. If necessary, convert the table to a range using Table Design > Convert to Range first, bearing in mind that this removes table functionality.

Subtotaling Excel table columns

For an Excel table, a Total Row is often a convenient way to show a subtotal. Click inside the table, open the Table Design tab, and select Total Row. Excel adds a row beneath the table. In the cell under the column you want to summarize, open the dropdown and choose Sum, Average, Count, or another available calculation. The Total Row uses a SUBTOTAL formula for many of these calculations, so its result can respond to table filters.

A table is useful when the list will grow: new records become part of the table, and its filters and formatting are built in. The Total Row is not the same as adding an ordinary formula immediately beneath a normal range. If you add rows to a table, Excel can extend the table and update its summary behavior. If you use a formula outside the table instead, check that its range expands when new records are added.

For a manually written formula using an Excel table column, structured references can make the purpose readable. For instance, a formula can refer to a table named Sales and its Amount column rather than to a cell range. Confirm the table and column names in your workbook before using a structured reference; Excel-generated names may differ from the example.

Common problems and how to fix them

  • The total does not change after filtering: Check that the formula is SUBTOTAL, not SUM, and that its reference includes the filtered data rows. Also confirm that the filter is applied to the range containing those rows.
  • The total is too small: If rows were hidden manually and should count, use a number from 1–11 instead of 101–111. Also make sure the formula range includes every intended value.
  • The total is too large: Check for values outside the intended range, or for a formula that adds a second range containing the same data. SUBTOTAL ignores nested SUBTOTAL formulas in a referenced range, which helps prevent double counting in grouped summaries, but it cannot correct unrelated duplicated values.
  • COUNT gives an unexpected result: Function number 2 counts numeric values; number 3 counts nonblank cells, including text. Choose the one that matches what you mean by count.
  • The formula displays as text or an error: Check that the cell is not formatted as Text, that the formula begins with an equals sign, and that the range reference is valid. In some regional settings Excel uses a different list separator, so the separator accepted by that installation may differ from the comma shown here.

SUBTOTAL also ignores other SUBTOTAL formulas within a reference, preventing a grand total from adding intermediate subtotals a second time. It does not ignore ordinary SUM formulas, so a range that includes an existing SUM result can still double-count. A practical fix is to keep summary formulas outside the detail range they summarize or use a carefully chosen range.

Choosing between SUBTOTAL, SUM, and grouped summaries

Choose SUBTOTAL when a result should respond to filtered records, when you want hidden-row behavior you can control, or when you are building a table Total Row. Choose SUM when you need a straightforward total that should include all numbers in the range regardless of filters. Choose the Data Subtotal command when the goal is to create visible summary rows at group changes in a sorted, ordinary range.

Before settling on a method, consider how the worksheet will be used:

  • Will readers filter the records and expect the displayed total to follow?
  • Should manually hidden rows count, or should they be excluded?
  • Do you need one overall figure, a total per group, or a reusable table summary?
  • Will new records be added regularly, requiring a range or table that expands?

For a single filtered column total, a SUBTOTAL formula or table Total Row is usually straightforward. For multiple group levels, the Data Subtotal tool can be useful, but a PivotTable may be a better fit if readers need to rearrange categories and compare summaries interactively.

Frequently Asked Questions

What formula adds a column in Excel but ignores filtered rows?
Use SUBTOTAL with function number 9 for a sum. With values in B2:B20, enter =SUBTOTAL(9,B2:B20). It sums visible rows and excludes rows hidden by a filter.

What is the difference between SUBTOTAL 9 and 109?
Both calculate a sum and both exclude filtered-out rows. Number 9 includes manually hidden rows, while 109 excludes manually hidden rows too.

Can SUBTOTAL calculate an average or count?
Yes. Use 1 for AVERAGE or 101 to average while excluding manually hidden rows. Use 2 for a count of numeric cells, or 3 for a count of nonblank cells; add 100 to those numbers to exclude manually hidden rows.

Can I subtotal more than one column?
Yes. You can use separate SUBTOTAL formulas for separate columns, which gives each column its own result. One formula can also accept multiple references, but it combines the referenced values into one result rather than returning a separate subtotal for each reference.

Why is the Subtotal command unavailable?
The Data Subtotal command does not work directly on an Excel table. Use a SUBTOTAL formula or a table Total Row instead, or convert the table to a range before using the command if grouped outline rows are what you need.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.