How do I count visible rows when using filters in Google Sheets?

Let’s say you have a table like this:

To find out how many animals are in the unfiltered view you can manually select all rows in the table and look to the bottom right:

But there has to be a better way, right?


Let’s just use the Power of Formulas!

We will use SUBTOTAL.

And now we count those rows!

Optionally, you can concatenate number with a text in the top row cell so you can view the number right at the top:

Enjoy your sheeeeets.


9 responses to “How do I count visible rows when using filters in Google Sheets?”

  1. Rachel

    Hello, I would like to use this formula but with specific criteria, for example only count the visible cells that show “N/A”. It seems to be difficult with Google Sheets. Thanks!

  2. Roman Luks

    Hi Rachel, first I would filter rows with “N/A” (you can filter data and select only the values you want using checkboxes) and then I would use the advice in this article to count ’em.

  3. Anonymous

    Or just use the COUNTIF and/or COUNTIFS functions.

  4. Josh

    Thank you! This did EXACTLY what I needed. This was SUPER helpful. This gave me a way to count-up while skipping those counts that are filtered out. EXACTLY what I needed. Much appreciated. [and very well (simply) explained!]

  5. Chris Yuill

    This is absolutely what I was trying to do.

    My list of radio stations that I was filtering by Format Type, Language, and location always showed the same count using CountA or similar functions.

    All I wanted to do was have a counter that changed to show the resulting count of stations which
    matched the criteria. Never would I have thought to use SubTotal as I’ve only ever seen it used on a
    shopping receipt.

    Now I’m off to learn more about this other part to your answer, “function_code”.

    Thank you very much,


  6. Chris Yuill

    After trying to use counta, countif, filter, etc with no luck, I found the solution here using SUBTOTAL.

    As there are often multiple ways to get the same result, would you please share how to use COUNTIF and/or COUNTIFS to show only the visible rows of a filtered list?


  7. ahmed hassan

    Very neat! Thanks!

  8. Tom Krohn

    Thanks for the post and lead on Subtotal function. I struggled to get it to work following your screen shots and example. But, I had success with this syntax:


    Where the first parameter of “3” triggers the CountA function in the range.

    Sharing in case this is helpful to others.

  9. Alan

    I had to google what that 103 meant but your example worked beautifully for me in google sheets. My exact syntax was =SUBTOTAL(103,B:B). The fact that it respects filters makes it perfect.

Leave a Reply

Your email address will not be published. Required fields are marked *