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?

Yeah!

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 Comments

  1. 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!

    1. 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.

    1. 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?

      Cheers!
      Chris

      1. 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:

        =subtotal(3,I7:I50)

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

        Sharing in case this is helpful to others.

  2. 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!]

  3. 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,

    Chris

Leave a Comment