To count visible Excel rows while excluding both filtered-out and manually hidden rows, use =SUBTOTAL(103,A2:A100) when every record has a nonblank value in column A. The result counts visible, nonempty cells in that range—not every row regardless of its contents—so choose a reliably populated column or use a helper column if records can be blank.
The SUBTOTAL formula and what it counts
In Microsoft Excel for Microsoft 365 on Windows, SUBTOTAL is usually the simplest formula for this job. Its first argument selects a calculation, and its remaining argument or arguments identify the cells to calculate. Function number 103 means COUNTA while also excluding rows hidden manually. SUBTOTAL also ignores rows removed from view by a filter.
For example, suppose A2:A100 contains an ID for each record. With that layout, =SUBTOTAL(103,A2:A100) counts the nonblank IDs in rows that remain visible. If five records are filtered out and two more rows are hidden with Excel’s Hide command, the result is the number of nonblank IDs among the rows still showing. It will not count blank cells as records.
Put the formula outside the filtered data range, such as in a summary cell above the table or in another column that is not part of the filtered list. Otherwise, the formula itself could be included in the range or hidden when you filter the data, making the result confusing.
Why 103 differs from 3
The first argument in SUBTOTAL determines how manually hidden rows are treated. The 1–11 function-number group includes manually hidden rows, but still excludes rows hidden by a filter. The 101–111 group excludes both filtered rows and manually hidden rows. The corresponding COUNTA choices are 3 and 103.
=SUBTOTAL(3,A2:A100)counts nonblank cells in the range, including manually hidden rows, but excluding filtered-out rows.=SUBTOTAL(103,A2:A100)counts nonblank cells in visible rows, excluding both manually hidden and filtered-out rows.
These formulas count cells, not row numbers. That distinction matters if the column you select contains blanks. For a numeric column, function numbers 2 and 102 provide COUNT behavior instead: they count numeric cells, with 102 excluding manually hidden rows. Choose the operation that matches the data you want counted; changing 3 to 103 does not make blank cells count.
How to apply a filter and count the results
To check a filtered list, select the data range and use Data > Filter, or work with an Excel table that already has filter buttons. Filter one or more columns as needed, then read the SUBTOTAL result. It updates as the visible set changes. You do not need to add a separate condition to the formula for each filter selection.
- Identify a column that contains a value in every record, such as a unique ID or required name.
- Enter the formula in a cell outside the rows that may be hidden or filtered.
- Apply or change filters, and compare the displayed result with the records still visible.
- If you also hide rows manually, confirm that the formula uses 103 rather than 3.
Filters and manual hiding are different ways of controlling what appears on screen, but SUBTOTAL handles both with the 101–111 function-number group. A normal COUNT or COUNTA formula does not adjust its result just because some of its referenced rows are hidden.
Counting visible rows when cells may be blank
If a record can have an empty cell in the column you count, SUBTOTAL with 103 may undercount visible records. For instance, if A2:A100 contains optional notes rather than required IDs, a visible row with no note contributes nothing. Selecting a different column that is always populated is the simplest fix.
When no existing column is reliably filled, add a helper column with the value 1 for every data row. Suppose the helper values are in B2:B100 and each data row has a 1. With that layout, =SUBTOTAL(109,B2:B100) sums the helper values for visible rows and therefore returns the visible row count. Function number 109 is SUM with manually hidden rows excluded; filtered-out rows are excluded as well. Check that every record has exactly one 1 and that the formula range does not include a summary row.
This method counts rows even when other fields in them are blank. It does require the helper values to stay aligned with the records. If you add new records, fill the helper formula or value into the new rows too. Converting the range to a table can make it easier to extend the data and its helper column consistently.
Using SUBTOTAL with an Excel table
Excel tables expand when records are added and provide named columns that can make formulas easier to read. If a table named Table1 has a required ID column named ID, with those table records as the data to count, use =SUBTOTAL(103,Table1[ID]). It counts the nonblank IDs in visible table rows and responds to the table’s filters.
Use the actual table and column names from your workbook. If the selected column contains blanks, this structured-reference formula has the same limitation as a regular cell range: COUNTA counts nonblank cells, not blank records. A helper column filled with 1s and a SUM-based SUBTOTAL can address that case. Keep the result outside the filtered table body so it remains visible when rows are filtered.
SUBTOTAL compared with COUNT and COUNTA
COUNT counts numeric values; COUNTA counts nonempty cells. Neither is designed to exclude hidden rows from its result. SUBTOTAL offers these calculation types while also responding to filtered rows and, with function numbers 101–111, manually hidden rows.
For example, if a visible record has the text value “A104” in its ID column, COUNTA-style SUBTOTAL includes it, but COUNT-style SUBTOTAL does not because the ID is text. If a row contains no value in the selected range, neither COUNTA nor COUNT includes it. For a visible-row total, choose COUNTA when each record has a populated text or numeric field, COUNT when the relevant values are numeric, or sum a helper column of ones when a row must count even if its other cells are empty.
SUBTOTAL also ignores other SUBTOTAL results in a referenced range. This helps prevent a subtotal from being counted again in an overall subtotal, but it is another reason to keep summary cells out of the data range you intend to count.
Common problems and checks
- The result is too small: Check whether the selected column has blank cells. COUNTA-style SUBTOTAL does not count them. Switch to a populated field or use a helper column.
- Manually hidden rows are still counted: Confirm that the formula starts with 103, not 3. Both ignore filtered rows, but only 103 also excludes manually hidden rows.
- The formula counts numbers but not text: A COUNT-based function number counts numeric cells. Use COUNTA behavior if text IDs or names should count.
- The result seems to include a total: Inspect the referenced range and remove headers, totals, or unrelated cells. SUBTOTAL excludes nested SUBTOTAL results, but an ordinary numeric or text total may still be counted depending on the chosen operation.
- The formula does not change after a filter: Confirm that the formula references the filtered data and that the filter is applied to those rows. A formula that references a different range cannot report the visible count of the intended list.
A useful check is to filter the list to a small, easy-to-verify group and compare the formula result with the visible records. Then clear the filter and check again. This can reveal a wrong range or an unpopulated helper column before the count is used in a report.
Frequently Asked Questions
Does SUBTOTAL ignore rows hidden by a filter?
Yes. SUBTOTAL excludes filtered-out rows for both the 1–11 and 101–111 function-number groups. Use a number from 101–111 when manually hidden rows should be excluded too.
What formula counts visible rows if some cells are blank?
Use a helper column containing 1 in every data row, then sum it with a SUBTOTAL function number that excludes manual hiding. For helper values in B2:B100, use =SUBTOTAL(109,B2:B100). The result is the number of visible rows only if every included record has a 1.
Can I count only visible rows that contain numbers?
Yes. If the numeric values are in C2:C100, =SUBTOTAL(102,C2:C100) counts numeric cells in visible rows and excludes rows hidden manually or by a filter. It does not count visible rows where column C is blank or contains text.
Will the count update when I hide a row manually?
It will if you use the 101–111 group, such as 103 for nonblank cells or 109 for a helper-column sum. The 1–11 group continues to include manually hidden rows.
Does this work on Mac or Excel for the web?
SUBTOTAL is available in Excel for Microsoft 365 on Windows, Mac, and the web. The formula logic is the same. Ribbon locations and interface details can vary, but the key requirement is to use the appropriate function number and reference the correct range.
Choosing the right approach
For most filtered lists, start with =SUBTOTAL(103,A2:A100) and replace the range with a column that is always filled for each record. This is compact, updates with filters, and excludes manually hidden rows. If no such column exists, use a helper column of 1s with function number 109. Before relying on the result, decide whether you mean visible nonblank cells, visible numeric cells, or every visible record; those are related but not identical counts.
Related
- Excel Sum of Squares: SUMSQ, Examples, and Common Uses
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Date Converter: Turn Text, Numbers, and TimestampsAn Excel date converter turns date-like text or numbers into usable Excel dates, or converts Excel dates into…
- How to Add Subtotal Rows in Excel by Category or DateTo add subtotal rows in Excel, sort your data by the field you want to group, select a…
- Google Sheets IF Function: Syntax, Examples, and TipsThe Google Sheets IF function checks whether a condition is true and returns one value if it is,…
- Inventory Spreadsheet Template for Google SheetsFor an inventory spreadsheet, use Google Sheets rather than Google Docs: Sheets is Google’s spreadsheet app, while Docs…
- Excel Dropdown List Box: Create, Edit, and TroubleshootIn Excel, a dropdown list box usually means an in-cell dropdown created with Data Validation: select a cell,…
- Word Labels: Create 10 Per Page and Print a Label SheetTo make ten labels on one page in Microsoft Word, open Mailings > Labels, choose Options, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.