Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum visible cells only in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUBTOTAL(9,B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Category | Amount |
| A | 100 |
| B | 200 |
| A | 150 |
| B | 300 |
| A | 250 |
=SUBTOTAL(9,B2:B6)→1000
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Apply a filter to your data (Data > Filter in Excel; Data > Create a filter in Sheets).
- 2In an empty cell outside the filtered range, type =SUBTOTAL(9,B2:B6) where B2:B6 is your numeric column.
- 3The result updates dynamically as you change filter criteria, always showing the sum of visible rows only.
Tips
- Function code 9 = SUM. Other useful codes: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN.
- SUBTOTAL ignores rows hidden by a filter but also ignores rows hidden manually (Format > Hide Rows). If you want to include manually hidden rows, use function codes 101–111 (e.g. 109 = SUM of filtered rows, including manually hidden).
- AGGREGATE (Excel only) is a more powerful alternative that can also ignore errors.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does my SUBTOTAL result not change when I apply a filter?
Make sure SUBTOTAL is placed outside the filtered range and that the range argument correctly covers the data rows. SUBTOTAL cells inside the filtered range may themselves be hidden.
Does Google Sheets support SUBTOTAL?
Yes — Google Sheets supports SUBTOTAL with the same function codes as Excel.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.