Count with Auto-Filter On

[sherry]

How do I count the number (rows) of cells visible on my screen when auto
filter is on and enabled and I am using it to count rows of data. For
instance, in one column, if I have the words red, orange, green, etc., typed
500 times and I use auto filter to only expose the word red, how do I find
out how many rows of "red" I have. Thanks soooo much!

 

[Mike H]

Try

=SUBTOTAL(103,A1:A10)

Where a1 - a10 is the unfiltered range

Mike

 

[Pete_UK]

Put this formula out of the filter range, e.g. insert a row at the top
of your sheet and put this in A1:

=SUBTOTAL(3,A3:A503)

assuming your colours (and the filters) are in column A.

Hope this helps.

Pete

 

[sherry]

Thanks so much. I have no idea where the 103 comes in. I was reading so
many other strings earlier to try and find this answer and I saw a very
similar formula with a 109 in it. I use formulas a lot, but I guess I'm a
novice at this because the 109 has me stumped too!

 

[Mike H]

have a look in Help for subtotal and you'll see it quite a handy tool

Mike

 

[Francisco Rodriguez]

this is indeed a very helpful post. I just learned a new, very essential
function. THANKS!

Francisco