Google Sheets SUBTOTAL Formula: Filtered Totals

How-to basics · Updated November 22, 2026

In Google Sheets, use =SUBTOTAL(9,B2:B7) to add the values in B2:B7 while excluding rows removed by a filter. The first argument, 9 in this example, selects the calculation; change it to another function code to count, average, or perform a different supported calculation.

How the SUBTOTAL formula works

SUBTOTAL is useful when a total should respond to filtering. Unlike a basic SUM, it automatically leaves out rows hidden by a filter, so the displayed total can represent only the records currently showing. This makes it a practical choice for filtered sales lists, inventory, survey results, and other tables where people view different subsets of the same data.

The function takes a calculation code followed by one or more ranges. For example, the code 9 means SUM, while code 1 means AVERAGE. The ranges tell Sheets which cells to examine. A SUBTOTAL formula can use more than one range, but make sure the ranges do not overlap unless you intend to count the overlapping cells more than once.

With amounts in B2:B7, the formula =SUBTOTAL(9,B2:B7) adds the numeric values in that range. If the six amounts are 120, 80, 50, 70, 30, and 100, the result with every row included is 450. If a filter hides some of those rows, the result updates to reflect the visible ones.

Function codes and what they calculate

Google Sheets provides two groups of codes. Codes 1 through 11 perform the listed calculation while including manually hidden rows; codes 101 through 111 perform the same calculation while excluding manually hidden rows. Both groups ignore rows excluded by a filter. The available calculations are:

  • 1 or 101: AVERAGE.
  • 2 or 102: COUNT, which counts numeric values.
  • 3 or 103: COUNTA, which counts non-empty cells.
  • 4 or 104: MAX, the largest value.
  • 5 or 105: MIN, the smallest value.
  • 6 or 106: PRODUCT, the product of the values.
  • 7 or 107: STDEV, the sample standard deviation.
  • 8 or 108: STDEVP, the population standard deviation.
  • 9 or 109: SUM.
  • 10 or 110: VAR, the sample variance.
  • 11 or 111: VARP, the population variance.

For instance, code 2 is useful when you need to count numeric entries rather than add them. Code 3 counts filled cells instead, including text entries. Choose the code based on what the cells contain and the question you want the subtotal to answer.

What happens when you filter rows

To filter a data range in the Sheets browser, select a cell in the data and choose Data > Create a filter. Use the filter controls in the header row to show only records that meet a condition. A SUBTOTAL formula referencing the records then recalculates without the rows the filter has hidden. You do not need to change its range or rewrite the formula for each filtered view.

Suppose column A contains categories and column B contains amounts: North has 120, 50, and 30; South has 80 and 100; and East has 70. With all six records visible, the total is 450. If you filter column A to show North only, =SUBTOTAL(9,B2:B7) returns 200, the sum of 120, 50, and 30. This is the key difference from an ordinary sum: the displayed result follows the filter.

Keep the subtotal formula outside the rows being filtered when possible. If the formula sits inside the filtered data, it may itself be hidden or the range may be harder to interpret. A common layout puts column headings in row 1, records in rows 2 through 7, and the subtotal below the table.

How manually hidden rows affect the result

Filtering and manually hiding a row are not the same thing. A row hidden because it fails a filter condition is excluded by every SUBTOTAL code. A row hidden manually using the row controls is included by codes 1 through 11 and excluded by codes 101 through 111. That distinction lets you decide whether a hand-hidden row should still contribute to a result.

With the six amounts 120, 80, 50, 70, 30, and 100 in B2:B7, suppose row 3, containing 80, is manually hidden and no filter is active. The formula =SUBTOTAL(109,B2:B7) returns 370, adding the five visible amounts. Using code 9 instead would still return 450 because that code includes manually hidden rows. If the row is hidden by a filter rather than manually, either code excludes it.

This difference matters in shared sheets: a person may hide a row just to reduce visual clutter without intending to change the total. If the total should reflect only filter selections but ignore manual hiding, use a code from 1 through 11. If manual hiding should also remove a row from the calculation, use the matching code from 101 through 111.

A worked example: sum, average, and count

Consider a small sales table with category labels in A2:A7 and the six amounts above in B2:B7. If you need the average amount across the visible records, use code 1. The formula =SUBTOTAL(1,B2:B7) returns 75 when all six rows are visible, because 450 divided by 6 is 75. If a filter leaves only North visible, the average becomes about 66.67, because (120 + 50 + 30) divided by 3 is about 66.67.

To count numeric amounts in B2:B7, use =SUBTOTAL(2,B2:B7). With the six sample numbers visible, the result is 6; with only the three North records visible, it is 3. This is different from counting labels. If every category cell in A2:A7 is filled, =SUBTOTAL(3,A2:A7) returns 6 with all rows visible, and 3 when the filter shows only the three North records.

These examples assume the amounts are actual numbers, not text that merely looks like a number. COUNT ignores text, and SUM cannot add text values as numeric amounts. COUNTA counts cells that are not empty, so a cell containing a space or a formula that returns an empty text string may still be counted. Check the underlying values if a count does not match what you see.

Choosing the right code and range

Start by asking two questions: what calculation do you need, and should manually hidden rows count? For a visible-row sum where manual hiding should not matter, code 9 is usually straightforward. For a total that should also disregard manually hidden rows, choose 109. Apply the same distinction to averages, counts, and the other calculations in the code list.

  • Use SUM codes 9 or 109 for totals.
  • Use AVERAGE codes 1 or 101 when the mean should follow visible records.
  • Use COUNT codes 2 or 102 for numeric entries, or COUNTA codes 3 or 103 for non-empty entries.
  • Use MAX or MIN codes when the highest or lowest visible value is what matters.

Check that the referenced range covers the records you want but not unrelated headings or notes. If new data is added below a fixed range, the formula will not automatically include it unless the range is extended. Keeping a consistent table layout and placing the subtotal beneath the data makes the range easier to audit and maintain.

Common mistakes and troubleshooting

A frequent misunderstanding is expecting SUBTOTAL to ignore every hidden row. Only rows hidden by a filter are excluded regardless of code; manually hidden rows require a 101–111 code to be excluded. Another common issue is using the wrong operation code: code 2 counts numbers, while code 3 counts non-empty cells, so their results can differ when a range contains labels or text.

If the result is unexpectedly zero or too small, inspect the cells for numbers stored as text, confirm the formula points at the correct column, and check whether the current filter has hidden records. If the result is too large, look for overlapping ranges or data that is included twice. Also check whether the chosen code includes manually hidden rows when it should not.

SUBTOTAL also ignores other SUBTOTAL formulas within its referenced range. This is intended to prevent a grand total from adding intermediate subtotal results a second time. It does not mean every other kind of calculation or helper value is automatically excluded, so keep the formula range limited to the source data you want to summarize.

The examples use commas between arguments, as in a US-style spreadsheet locale. Depending on the spreadsheet locale, Sheets may display or require semicolons as argument separators. If a valid-looking formula produces a parsing error, check the file’s locale and use the separator expected by that sheet.

SUBTOTAL compared with SUM and other options

SUM is the simpler choice when the answer should always include every number in its range. It does not adjust its result to exclude filtered-out rows. SUBTOTAL is preferable when viewers will filter the data and need the displayed calculation to describe only the records left visible. In Excel, SUBTOTAL has the same general purpose and similar function codes, though this page’s menu instructions are for Google Sheets in a browser.

For a one-off total of a particular category, a conditional calculation may be more suitable than filtering a table. For a live total that should change as someone applies filters, SUBTOTAL is often more convenient because it responds to the filter state without requiring a separate condition in the formula. The choice depends on whether the calculation should follow the visible view or a fixed rule.

Frequently Asked Questions

Does SUBTOTAL ignore rows hidden by a filter?
Yes. Google Sheets excludes rows removed from view by a filter for all supported SUBTOTAL codes. The 101–111 codes add the separate behavior of excluding rows hidden manually.

What is the SUBTOTAL code for a sum?
Use 9 to sum a range while including manually hidden rows, or 109 to sum while excluding manually hidden rows. Both omit rows hidden by a filter.

Can SUBTOTAL calculate an average?
Yes. Use code 1 to average the values in the referenced range, or 101 if manually hidden rows should be excluded. Filtered-out rows are excluded either way.

Why does my subtotal not change when I hide a row?
If you hide the row manually and use a code from 1 through 11, it remains part of the calculation. Switch to the matching code from 101 through 111 if manual hiding should remove it. Rows hidden by a filter are excluded automatically.

Does SUBTOTAL count text cells?
It depends on the code. COUNT codes 2 and 102 count numeric values; COUNTA codes 3 and 103 count non-empty cells, including text. The chosen code determines what qualifies.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.