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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “excel count only visible cells” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. 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. That turns a layout choice that keeps the sheet readable and sortable into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “excel count only visible cells”, 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.