When working with large datasets in Google Sheets, applying filter views or manually hiding rows often creates calculation discrepancies. Standard mathematical functions like SUM, AVERAGE, and COUNTA calculate all cells in a target range regardless of whether those rows are currently visible or hidden. The SUBTOTAL function solves this issue by recalculating summary values dynamically based strictly on visible data.
Syntax and Function Codes of SUBTOTAL
The syntax for the SUBTOTAL function requires a mathematical operation code followed by one or more cell ranges:
=SUBTOTAL(function_code, range1, [range2, ...])The function_code determines which calculation is performed. In Google Sheets, function codes from 1 to 11 automatically ignore rows hidden by data filters. Codes from 101 to 111 ignore rows hidden by data filters and rows manually hidden via right-click:
| Function Name | Filter-Only Code (1-11) | Filter & Manual Hide Code (101-111) |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT (numbers only) | 2 | 102 |
| COUNTA (non-empty) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| SUM | 9 | 109 |
Calculating Dynamic Sums on Filtered Lists
Consider an inventory or sales sheet where transactions are stored in column C (from cell C2 to C100). When users apply a filter to view only specific regions or product categories, using standard SUM displays the static grand total of all records.
To calculate the sum of only the currently filtered rows, use code 9:
=SUBTOTAL(9, C2:C100)Whenever you change the active filter criteria, the formula recalculates instantly to represent only the visible transaction amounts.
Ignoring Manually Hidden Rows with Code 109
If your team members periodically right-click rows and select Hide row, function code 9 will still include those rows in the total because they were hidden manually rather than through a data filter.
To exclude both filtered-out rows and manually hidden rows, use code 109:
=SUBTOTAL(109, C2:C100)This ensures your report metrics reflect only data that is visually apparent on the worksheet.
Counting Filtered Records with COUNTA (Code 103)
To display a dynamic counter showing how many active records match your filter criteria:
="Showing " & SUBTOTAL(103, A2:A100) & " matching records"This counts all non-empty cells in the text column A, giving viewers immediate feedback on the size of the filtered dataset.
Preventing Double Counting with Nested Subtotals
One of the most powerful architectural advantages of SUBTOTAL is its immunity to double counting. If your data range contains intermediate subtotal rows throughout the sheet, an overarching SUBTOTAL grand total formula placed at the bottom will automatically skip any intermediate SUBTOTAL formulas inside the range.
This eliminates the need to construct cumbersome addition chains like =E15 + E30 + E45, providing clean, robust reporting models.