In Excel: use =SUBTOTAL(103,A2:A100) to count the non-empty cells in rows that are still visible, or =SUBTOTAL(102,A2:A100) to count only visible numbers; COUNTA and COUNT keep counting hidden and filtered rows.
On this page7
Choose a column that is filled in every data row, such as an ID or name column.
In a cell above or beside the table, enter =SUBTOTAL(103,A2:A100) with that column's range.
Use 102 instead of 103 if the column holds numbers and only numeric cells should count.
Apply or change the filter and check that the count equals the number of visible rows.
To count visible rows that also meet a condition, filter on that condition first, then read the SUBTOTAL result.
What this does
COUNT, COUNTA and COUNTIF look at every cell in their range, whether the row is shown or not. SUBTOTAL counts only rows that are still displayed: 3 and 103 behave like COUNTA, 2 and 102 like COUNT. Rows removed by a filter are excluded by all four numbers, while rows hidden with Hide are excluded only by 102 and 103. Pick a column that has a value in every row you want to count — a blank cell in a visible row is not counted by either version. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “count filtered rows in excel”. Start on a copy or a tiny sample, keep the affected rows visible, and compare the result with the outcome you expected before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
A2:A6 lists five order IDs and a filter on Region hides two of them. =COUNTA(A2:A6) still returns 5; =SUBTOTAL(103,A2:A6) returns 3, matching the three rows on screen and the "3 of 5 records found" message in the status bar. If you then hide one of the visible rows by hand, =SUBTOTAL(3,A2:A6) stays at 3 while =SUBTOTAL(103,A2:A6) drops to 2. A count of visible rows is how many records a filter actually returns — the number a filtered report or a quick check needs, not the size of the whole table. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “count filtered rows in excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Using
=COUNTA()or=ROWS()and expecting filtered rows to be left out. - Counting a column with blanks, which undercounts visible rows that have an empty cell there.
- Using 3 instead of 103 when rows are hidden by hand rather than by a filter.
- Putting the formula in a row the filter can hide.
Frequently asked questions
Why does COUNTIF still count hidden rows?
COUNTIF has no option for row visibility; it evaluates every cell in its range. Filter first and use SUBTOTAL, or add a helper column with =SUBTOTAL(103,A2) and count the rows where it is 1.
Where does Excel show the number of filtered rows?
After filtering, the status bar shows a message such as "3 of 5 records found"; SUBTOTAL puts the same count into a cell you can reference.
What is the difference between 3 and 103?
Both skip rows removed by a filter. 3 still counts rows hidden with Hide; 103 skips those too.