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.
Leave a Reply to Chris Yuill