Count Visible Rows in a Filtered List? Easy Methods

This article explains how to count only the rows that remain visible after you apply a filter in Excel or Google Sheets. You will learn the exact formulas and steps to get the count of visible rows, and how to avoid common mistakes that lead to wrong totals.

Formula

The core idea is simple: the number of visible rows is the total rows minus the hidden rows. But in a spreadsheet, you don’t always know the hidden count directly. The most reliable way to count visible rows is to use the SUBTOTAL function in Excel or the SUBTOTAL function in Google Sheets (they work the same way).

The formula is:

=SUBTOTAL(3, range)

Where:

• 3 is the function number for COUNTA (counts non-empty cells). Use 2 for COUNT (counts numeric cells only).
• range is the column or cells you want to count, e.g., A2:A100.

This function automatically ignores rows hidden by a filter. If you have manually hidden rows (right-click → Hide), use SUBTOTAL(103, range) to ignore those as well.

Worked Example

Suppose you have a list of 100 orders in column A (A2:A101). After applying a filter to show only orders from a specific region, you see 30 rows. To count them:

1. Click on an empty cell, say C1.
2. Type =SUBTOTAL(3, A2:A101).
3. Press Enter. The cell shows 30.

That matches the default values in the calculator above: 100 total rows, 30 visible, giving 30% visible. The formula gives the exact count without you having to manually count.

Why Not Just Use COUNTA?

A common mistake is using =COUNTA(A2:A101) which counts all non-empty cells, including hidden ones. That gives 100, not 30. The SUBTOTAL function is specifically designed to respect filters. It’s the standard tool for this task.

How to Count Visible Rows in Excel

Here are the steps for Microsoft Excel (2016, 2019, 365, and similar):

1. Select any cell inside your data range.
2. Go to the Data tab and click Filter (or press Ctrl+Shift+L). This adds dropdown arrows to your header row.
3. Click the dropdown arrow on the column you want to filter by.
4. Uncheck Select All and then check the specific values you want to see.
5. Click OK. The list now shows only the matching rows.
6. In an empty cell, type =SUBTOTAL(3, A2:A101) (adjust the range to your data).
7. Press Enter. The result is the count of visible rows.

You can also use the status bar: after filtering, Excel shows “Countâ€� in the bottom-right status bar if you select the visible cells. But that only works if you select the cells manually, and it doesn’t update automatically like a formula.

How to Count Visible Rows in Google Sheets

Google Sheets works almost identically:

1. Select your data range.
2. Click Data → Create a filter (or use the filter icon in the toolbar).
3. Click the filter icon in the column header, uncheck values you don’t want, and click OK.
4. In an empty cell, type =SUBTOTAL(3, A2:A101).
5. Press Enter. The visible row count appears.

Alternatively, you can use =SUBTOTAL(103, A2:A101) to also ignore manually hidden rows. The difference between 3 and 103 is that 3 ignores only filtered rows, while 103 ignores both filtered and manually hidden rows.

Common Mistakes and How to Avoid Them

• Using COUNTA instead of SUBTOTAL – gives the total including hidden rows.
• Wrong range – if your data has blank cells, SUBTOTAL(3) counts non-empty cells. If you have blanks, use SUBTOTAL(2) to count only numbers, or adjust the range to exclude blanks.
• Manual hiding – if you hide rows manually (not via filter), SUBTOTAL(3) still counts them. Use 103 to ignore them.
• Filtering multiple columns – SUBTOTAL works on the range you specify, so if you filter by multiple columns, it still counts only the visible rows in that range.

Alternatives to SUBTOTAL

If you need to count visible rows based on a condition (e.g., count only visible rows where the value is “Yesâ€�), you can use SUMPRODUCT with the SUBTOTAL offset trick, but that’s advanced. For simple counting, SUBTOTAL is the best. Another option is to use the AGGREGATE function in Excel (function number 2 or 3) which also ignores hidden rows, but it’s more complex and not available in Google Sheets.

Frequently Asked Questions

Does SUBTOTAL count hidden rows if I hide them manually?

No, if you use function number 103 (e.g., =SUBTOTAL(103, A2:A100)), it ignores manually hidden rows. Function number 3 (without the 1 prefix) ignores only rows hidden by a filter, not manual hides.

Can I count visible rows without a formula?

Yes, after filtering, select the visible cells in the column, and look at the status bar in Excel. It shows “Countâ€� if you have numeric or text data. But this is manual and doesn’t update automatically.

Why does my SUBTOTAL return 0?

This usually happens if the range contains only blank cells or if you used function number 2 (COUNT) but the cells contain text. Use 3 for text and numbers, or ensure your range includes data.

Does SUBTOTAL work in Google Sheets?

Yes, SUBTOTAL works in Google Sheets exactly as in Excel, with the same function numbers. The syntax is identical.

How do I count visible rows in a filtered list with multiple columns?

SUBTOTAL works on the range you give it. If you filter by multiple columns, the visible rows are those that pass all filters. SUBTOTAL will count only those visible rows in the specified range.

What is the difference between SUBTOTAL(3) and SUBTOTAL(103)?

The difference is that 3 ignores only rows hidden by a filter, while 103 ignores both filtered and manually hidden rows. Use 103 if you want the count to reflect any hidden rows, not just filtered ones.

Related

• Skype Keyboard Shortcuts: The Complete Cheatsheet
• System Call Overhead: Why Kernel Requests Take Time
• Control AI Agent Access to Sensitive Data & Actions

in t | Total rows in list | rows | 100 | 1
in v | Visible rows after filter | rows | 30 | 1
out pct | Visible percentage | % | (v / t) * 100 | 1