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.

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!

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.

Or just use the COUNTIF and/or COUNTIFS functions.

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

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

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

Very neat! Thanks!

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.

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.