The formula
Sum of visible rowsExcelGoogle Sheets
=SUBTOTAL(109,C2:C7)In E2 → 585
Excel: also ignore error valuesExcel
=AGGREGATE(9,5,C2:C7)In E3 → 585
Plain SUM (for comparison)ExcelGoogle Sheets
=SUM(C2:C7)In E4 → 740
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | ||
| 2 | East | Pens | 120 | 585 | |
| 3 | West | Pens | 95 | 585 | |
| 4 | East | Paper | 180 | 740 | |
| 5 | North | Pens | 60 | ||
| 6 | West | Paper | 210 | ||
| 7 | East | Ink | 75 |
In the example, rows 3 and 5 (West) are hidden.
How it works
- 109
- SUM, ignoring hidden and filtered rows. 9 ignores only filtered rows. 101–111 are AVERAGE, COUNT, MAX, … with the same rule.
- AGGREGATE(9,5,…)
- 9 = SUM, 5 = ignore hidden rows. Use 7 to ignore hidden rows and errors.
Common errors and fixes
Hidden columns still counted?
SUBTOTAL only ignores hidden rows, not columns.
Total doesn't change when filtering?
You used SUBTOTAL(9,…) with manually hidden rows. Use 109.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.