Excel SUBTOTAL Formula Examples for Filtering and Totals

With sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70, and 180, the result is 600. SUBTOTAL is especially useful when you want a total to respond to filtered rows, and its function number lets you choose a sum, average, count, or other calculation.

What SUBTOTAL does

SUBTOTAL calculates a result from one or more cell references. Unlike a plain SUM, it is designed to work with filtered lists: rows removed from view by an AutoFilter are excluded from the calculation. This makes it useful for reports where someone can filter a table by department, month, product, or another field and still see a meaningful total for the visible records.

In Excel for Microsoft 365 on Windows, the function syntax is SUBTOTAL(function_num, ref1, [ref2], …). The function number selects the calculation, and the reference arguments specify the cells to use. You can provide multiple references, but SUBTOTAL accepts references rather than a general expression such as a multiplication of two ranges. For more complicated calculations, calculate the needed values in helper cells and subtotal those results.

SUBTOTAL also has a special rule for nested subtotals: if a referenced range contains another SUBTOTAL formula, that nested result is ignored. This helps prevent a grand total from adding both the detail rows and their already-calculated group totals.

Basic sum, average, and count examples

Suppose B2:B6 contains the five sales values used above. The examples below use a reference to that range, so they can recalculate when rows are filtered. Each function number changes the operation while the range stays the same.

To sum the values, with sales in B2:B6, use =SUBTOTAL(9,B2:B6). The result is 600.
To average the values, with sales in B2:B6, use =SUBTOTAL(1,B2:B6). The result is 120, because 600 divided by five values is 120.
To count numeric cells, with sales in B2:B6, use =SUBTOTAL(2,B2:B6). The result is 5.
To find the largest value, with sales in B2:B6, use =SUBTOTAL(4,B2:B6). The result is 180.
To find the smallest value, with sales in B2:B6, use =SUBTOTAL(5,B2:B6). The result is 70.

For a nonempty-cell count, the reference can point to a text column. With category names in A2:A6 and all five cells filled, =SUBTOTAL(3,A2:A6) returns 5. Function number 3 counts nonempty cells, including text; function number 2 counts numeric cells only. If your reference includes a header, COUNTA-style counting can include that header, so use the data rows rather than the heading when you want a record count.

Function numbers and manually hidden rows

SUBTOTAL offers two sets of function numbers. Numbers 1 through 11 include manually hidden rows but exclude rows removed by a filter. Numbers 101 through 111 exclude both filtered-out rows and manually hidden rows. The corresponding calculation is the same in each pair: 1 and 101 mean AVERAGE, 2 and 102 mean COUNT, 3 and 103 mean COUNTA, 4 and 104 mean MAX, 5 and 105 mean MIN, 6 and 106 mean PRODUCT, 7 and 107 mean STDEV.S, 8 and 108 mean STDEV.P, 9 and 109 mean SUM, 10 and 110 mean VAR.S, and 11 and 111 mean VAR.P.

For example, assume sales in B2:B6 are 120, 80, 150, 70, and 180, and row 3—the row containing 80—has been hidden manually using the row heading. With sales in B2:B6, =SUBTOTAL(9,B2:B6) returns 600 because function number 9 includes manually hidden rows. With the same layout, =SUBTOTAL(109,B2:B6) returns 520 because 600 minus the hidden value 80 is 520. If a filter also hides rows, both formulas exclude the filtered-out rows.

Choose the 1–11 set when manual hiding should not change the result, such as when rows are hidden only for presentation. Choose the 101–111 set when hidden rows should be treated as excluded data. A worksheet filter is different from manually hiding a row: either function-number set excludes rows filtered out of the list.

Subtotal a filtered list

Imagine A2:A6 contains Office, Hardware, Office, Hardware, and Office, while B2:B6 contains 120, 80, 150, 70, and 180. If you filter column A to show Hardware only, the visible sales are 80 and 70. With sales in B2:B6, =SUBTOTAL(9,B2:B6) returns 150 while that filter is active. Clear the filter and the same formula returns 600 again.

This automatic adjustment is the key difference from SUM. With the same sales range, =SUM(B2:B6) still returns 600 when a filter hides the Office rows, because SUM does not ignore filtered-out records. A SUBTOTAL formula is therefore a strong choice for a visible-results total, while SUM is appropriate when the intention is always to include every value in the range.

To create 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 category heading to select the desired item. If the range has headings and is likely to grow, convert it to an Excel table with Insert > Table; tables automatically provide filter controls and normally extend formulas and formatting as rows are added. In Excel for Mac, the Filter command is also on the Data tab, though ribbon layout can vary. Excel for the web supports filtering and SUBTOTAL formulas, but some desktop commands and ribbon arrangements differ.

Group totals and grand totals

For a report with separate subtotals, sort the records by the grouping field first so that each group is together. For example, suppose rows 2:4 hold the first group’s sales in B2:B4 and rows 6:8 hold the second group’s sales in B6:B8, with labels or a blank row separating them. Put each group subtotal beside its group, then place a grand total below both groups.

With the first group’s detail values in B2:B4, enter =SUBTOTAL(9,B2:B4) in its subtotal cell. If those values are 120, 80, and 150, the result is 350.
With the second group’s detail values in B6:B8, enter =SUBTOTAL(9,B6:B8) in its subtotal cell. If those values are 70, 180, and 100, the result is 350.
With both detail groups and their subtotal cells between rows 2 and 9, enter =SUBTOTAL(9,B2:B9) in the grand-total cell. It returns 700: the detail values total 350 plus 350, and the nested SUBTOTAL results are ignored.

When the groups are arranged in a simple list, Excel can insert outline subtotal rows for you. On Windows, select a cell in the sorted range and choose Data > Outline > Subtotal. In the dialog, choose the field to group by, the calculation, and the column to subtotal. The command inserts subtotal rows and an outline that lets you collapse or expand detail. It is intended for a normal range, not an Excel table; if the command is unavailable for a table, convert the table to a range before using it, or create formula-based totals instead. The formula approach is also easier to customize and maintain in a dashboard.

Choosing the right function number

For everyday totals, the most useful choices are often 9 for SUM, 1 for AVERAGE, 2 for COUNT, and 3 for COUNTA. The distinction between COUNT and COUNTA matters: a column containing numbers and blanks should use 2 if the question is how many numeric entries are visible, while a column containing text identifiers should use 3 to count nonempty visible records.

Use 4 and 5 for the visible maximum and minimum. Use 6 for PRODUCT, and 7 or 8 when you specifically need a sample or population standard deviation. The variance options 10 and 11 likewise distinguish sample from population calculations. If you are unsure about a statistical choice, check what the underlying data represents; the function number does not decide whether a sample or full population is appropriate.

For clarity, put the formula in a total row outside the rows being summarized. The function ignores nested SUBTOTAL results, but placing the result outside its own reference is still easier to read and reduces the chance of confusing overlapping ranges. Label the result, such as Visible sales, if readers might otherwise assume it includes filtered-out records.

Common problems and checks

The total does not change after filtering: Confirm the formula uses the correct range and that the filtered records are actually excluded by the filter. A normal SUM will not respond to filtering. Also check that the formula is not pointing to a different column or a fixed range that omits newer rows.
The answer seems too large: Check whether manually hidden rows should be excluded. If so, use the 101–111 function-number family rather than 1–11. Also check for duplicate detail records; SUBTOTAL does not identify duplicates or validate the data.
An average shows an error: AVERAGE via SUBTOTAL needs at least one visible numeric value. If a filter leaves no numeric values in the referenced range, there is nothing to average. Decide how a report should display that situation rather than assuming it is a calculation bug.
A count is off by one: COUNTA counts text and other nonempty cells, including a heading if the heading is inside the reference. Use only the data rows, and choose COUNT instead if only numeric cells should count.
New rows are missing: Expand the referenced range or use an Excel table. A table’s structured reference can make a formula easier to maintain. With sales values in the Sales column of a table named Table1, =SUBTOTAL(9,Table1[Sales]) sums that column and responds to its filters.

SUBTOTAL does not remove records, change their values, or decide which rows are relevant; it only calculates over its references according to the selected function number and visibility rules. Review filters before sharing a report, because a visible-only result can be correct for the current view but misleading if readers assume it represents the full dataset.

SUBTOTAL compared with SUM and alternatives

SUM is simpler when every cell in the range should always count. SUBTOTAL is preferable when filters are part of the way people inspect the data, or when a grand total contains group subtotal rows that should not be added twice. Neither function repairs missing values, removes duplicates, or checks whether the underlying records are accurate.

For an Excel table, SUBTOTAL is often a natural choice for a total that follows table filters. Excel tables also offer a Total Row: select a cell in the table, open the Table Design tab, and turn on Total Row. In the total-row cell for a column, select a calculation from its dropdown; Excel commonly uses SUBTOTAL for table total-row calculations so the result responds to filters. The exact visible controls can vary with window size and Excel build. A PivotTable is another option when the goal is to group, summarize, and rearrange larger sets of records rather than maintain a handful of formulas.

Frequently Asked Questions

Does SUBTOTAL ignore rows hidden by a filter?
Yes. Both function-number sets, 1–11 and 101–111, exclude rows removed by a filter. The difference is that 101–111 also exclude rows hidden manually.

What is the difference between SUBTOTAL 9 and 109?
Both calculate a sum. Function number 9 includes manually hidden rows, while 109 excludes them. Both exclude rows filtered out of the list.

Can SUBTOTAL count visible text entries?
Yes. Use function number 3 for COUNTA behavior, which counts nonempty cells, including text. Use 103 instead if manually hidden rows should also be excluded. Keep headings outside the referenced data range to avoid counting them.

Why does SUBTOTAL return zero or an unexpected result?
Check the function number, the referenced cells, and the current filter. SUM-style SUBTOTAL calculations add numeric values; text numbers stored as text may not behave like numeric entries. For counts, make sure you selected COUNT or COUNTA according to the type of entries you want to count.

Can I use SUBTOTAL inside an Excel table?
Yes. A structured reference such as Table1[Sales] can refer to a table column, and the formula responds to table filtering. A table’s Total Row is also a convenient way to add a filter-aware total without placing a separate formula below the table.

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.