How to count only visible rows after filtering
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)A count that respects the filter — "showing N of M records".
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =SUBTOTAL(103,A2:A1000) | 103 = COUNTA over visible rows only. Put it ABOVE the data or beside it, not in a filterable row. |
| Google Sheets | =SUBTOTAL(103,A2:A1000) | Identical. |
| LibreOffice Calc | =SUBTOTAL(103,A2:A1000) | Identical. |
How it works
Plain COUNTA keeps counting rows a filter has hidden; SUBTOTAL with the 100-series COUNTA code (103) counts only what's visible (all 3 here since nothing's filtered — apply a filter and the number drops live). A "showing X of Y" header is =SUBTOTAL(103,A2:A1000)&" of "&COUNTA(A2:A1000). Codes 109/101 do the same for SUM/AVERAGE, and the full code table lives in the SUM-vs-SUBTOTAL comparison. Place the formula outside the filtered range or it can hide itself.
Verified, not just documented
We ran =SUBTOTAL(103,A2:A4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
SUBTOTAL — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count cells by color (and why formulas can't see color)
- How to sum only the filtered (visible) rows