Count Excel Rows After a Filter: Formulas That Work

To count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every data row. With nonblank IDs in A2:A100, enter =SUBTOTAL(103,A2:A100) outside the filtered list; the result is the number of visible IDs, and therefore the visible data rows. This count updates when you change the filter.

The quick method: count visible records

Excel filters hide rows that do not meet the selected criteria; they do not delete those records. A regular count can therefore include rows you cannot currently see. SUBTOTAL is designed to calculate over a list while excluding rows hidden by a filter.

The formula above uses function number 103, which tells SUBTOTAL to count nonempty cells and ignore both filtered-out rows and rows hidden manually. For a reliable row count, use a field such as an order number, employee ID, or record key that is filled in for every row. If four of ten records remain visible and each has an ID, the formula returns 4.

Put the formula in a cell outside the filtered data range, such as above the table or in a separate summary area. If you put it inside the list being filtered, it may itself be hidden or included in the filtered range, making the result confusing. Keep the formula’s reference limited to the data rows rather than including a heading.

Why SUBTOTAL works with filters

SUBTOTAL performs a selected calculation on a range and treats filtered-out rows differently from ordinary functions such as COUNTA. When a filter hides records, SUBTOTAL excludes those rows from its calculation. The result changes as you apply, clear, or adjust the filter, so you do not need to edit the formula each time.

The function number also determines how manually hidden rows are handled. Numbers 1 through 11 include manually hidden rows, while 101 through 111 ignore them; both groups exclude rows removed from view by a filter. Number 3 means COUNTA, or count nonempty cells. Number 103 does the same count but also leaves out manually hidden rows.

That distinction matters when someone has hidden rows using Excel’s Hide command in addition to applying a filter. Use 103 when you want a count of everything currently visible on the worksheet. Use 3 when manually hidden rows should still count, but filtered-out rows should not.

Apply a filter and check the count

In Excel for Microsoft 365 on Windows, click a cell in the data range and choose Data > Filter. Excel adds dropdown arrows to the column headings; open a dropdown and choose the values or conditions to display. You can also toggle AutoFilter with Ctrl+Shift+L on Windows. On Mac, the Data > Filter ribbon command is a straightforward option; keyboard shortcuts can differ by Excel version and keyboard settings.

Identify a column with a value in every record, such as a unique ID.
Apply the filter to your list or Excel table.
Enter the SUBTOTAL formula in a separate summary cell, using only the data cells in that ID column.
Change the filter and confirm that the result changes to match the visible records.

For example, suppose A2:A11 contains ten order IDs, with no blank IDs, and a filter leaves four orders visible. With that layout, =SUBTOTAL(103,A2:A11) returns 4. If you clear the filter and all ten rows become visible, the result becomes 10, unless some rows are manually hidden, which 103 also excludes.

Rows are not the same as nonblank cells

A key detail is that the formula counts nonempty cells in the range, not worksheet rows directly. It works as a row count only when the chosen column has one nonblank value for every record. If some visible records have a blank in that column, they will not be counted, even though their rows are on screen.

For instance, if five records are visible but one has no value in the column referenced by the formula, SUBTOTAL(103,...) returns 4. This is not a filter error; it is the expected result of counting nonempty cells. Choose a different, consistently populated field if your data has one.

If no existing field is complete, add a helper column with a 1 in every data row. For a list with one helper value in C2:C100 per record, use =SUBTOTAL(9,C2:C100) to sum the visible ones. If five records are visible, each with a 1, the result is 5. Function number 9 includes manually hidden rows, while still excluding rows hidden by the filter. To exclude manually hidden rows too, use 109 instead: with the same helper values and five rows visible, =SUBTOTAL(109,C2:C100) returns 5 if none of those five is manually hidden.

A helper column is useful when every record must count regardless of whether a particular field is blank. Keep the helper values consistent: an accidentally missing 1 or an extra value can make the total misleading.

Using the formula in an Excel table

Excel tables make it easier to work with data that grows over time. When a range is formatted as a table, its column names can be used in structured references, and its filter dropdowns operate on the table’s records. A structured reference is often easier to understand than a long range address.

With an Excel table named Sales and a column named Order ID that is filled for every record, use =SUBTOTAL(103,Sales[Order ID]) in a cell outside the table. The formula counts the nonblank Order ID cells still visible after a table filter. As rows are added to the table, the reference can expand to include the new records.

Excel tables can also show a Total Row. Select a cell in the table, open the Table Design tab, and enable Total Row. The dropdown in a Total Row cell offers summary calculations, including options based on SUBTOTAL. This can be convenient for a count that stays with the table, but check which column the total uses: a count of a column with blanks will not equal the number of visible records.

Why ordinary counts can give a different answer

Functions such as COUNT and COUNTA count values in a range without adapting to the filter in the same way. COUNT counts numeric entries, while COUNTA counts nonempty cells; neither is a substitute for a filter-aware subtotal when you need the visible-record count. A formula that counts a particular category can also include matching records hidden by the filter.

There is also a difference between counting records and counting values. If a filtered list has four visible rows and each row has three filled columns, counting all the selected cells could produce 12, not 4. The dependable approach is to use one populated cell per record or a helper column containing one value per row.

The status bar at the bottom of Excel may show a Count for selected cells, but that number describes nonempty selected cells, not necessarily records. It can be useful for a quick check if you select one reliable column, but a SUBTOTAL formula is clearer, repeatable, and easier to include in a report.

Common problems and how to fix them

The result is too small: Check whether the chosen column has blanks in visible records. Switch to a complete ID column or add a helper column with one value per row.
The result includes manually hidden rows: Change function number 3 to 103, or 9 to 109 for a helper-column sum. The 100-series versions exclude manually hidden rows.
The result does not change: Confirm that the filter is applied to the same list covered by the formula and that the formula range includes the records you expect. Also make sure the formula cell is outside the filtered range.
The heading is counted: Start the range at the first data row, not the heading. A text heading is nonblank and can increase a COUNTA-style count if included.
The formula returns zero: Check that the referenced cells contain values and that at least one matching record is visible. If the reference points to the wrong column or an empty range, the count will not represent your list.
The count seems one too high: Look for a stray value below the data, an extra row included in the reference, or a formula in a cell that should be outside the records.

When auditing a result, compare it with the visible IDs rather than counting across several columns. If the workbook contains multiple filter ranges or manually hidden rows, test the formula after clearing filters and then after applying one filter at a time; that helps distinguish a reference issue from the intended hidden-row behavior.

Frequently Asked Questions

How do I count only visible rows in Excel?
Use SUBTOTAL with function number 103 on a column that has a value in every record, for example =SUBTOTAL(103,A2:A100) when A2:A100 contains nonblank IDs. It excludes filtered-out and manually hidden rows.

Does SUBTOTAL update when I change a filter?
Yes. SUBTOTAL recalculates to exclude rows hidden by the filter. Applying a different filter, clearing it, or changing its selections changes the visible count automatically.

What is the difference between SUBTOTAL 3 and 103?
Both count nonempty cells and exclude filtered-out rows. Function number 3 includes manually hidden rows; function number 103 excludes them too.

Can I count filtered rows if the ID column has blanks?
Not accurately with a COUNTA-style SUBTOTAL on that column, because blank cells are not counted. Use another field that is always populated, or create a helper column with one value for each data row and sum it with SUBTOTAL.

Why does COUNT not match the number of filtered rows?
COUNT counts numeric cells, not rows, and it does not exclude filtered-out records the way SUBTOTAL does. Use a filter-aware SUBTOTAL on one consistently populated field to count visible records.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.