How to count the number of rows of a filtered column!

• Question

• Hi,
Here's my situation :

I've filtered my data using the 2nd column in excel(2003). I want to count the number of rows. It's 154 in total but after filtering it should reduce. But I'm not able to get the exact count. can anyone help me with this?
Thanks
Justin
Tuesday, September 13, 2011 11:46 AM

• Try this where the reference refers to the filtered column

=SUBTOTAL(3,A2:A300)

See SubTotal in help, in particular for other function numbers, eg you might prefer 2 for Count rather than 3 for Counta

I see you have left a comment in your other recent thread, it still remains "unanswered" so can it now be closed.

Peter Thornton

Tuesday, September 13, 2011 11:56 AM

All replies

• Try this where the reference refers to the filtered column

=SUBTOTAL(3,A2:A300)

See SubTotal in help, in particular for other function numbers, eg you might prefer 2 for Count rather than 3 for Counta

I see you have left a comment in your other recent thread, it still remains "unanswered" so can it now be closed.

Peter Thornton

Tuesday, September 13, 2011 11:56 AM
• Thanks Peter!

I did try the formula but it didn't quite work. It gave me the same result.

Actually when a column is filtered, (lets say) after row 7 comes row 13 (since it's filtered). So there's a gap of 6 rows.

I need the count in such a way that if the count is 7 at the 7th row, it should become 8 for the next row (13th row).

Also how do I close an opened thread? (Sorry I'm quite new to forums)

Thanks

Justin

Tuesday, September 13, 2011 12:12 PM
• Afraid I don't follow. Maybe show some data to illustrate, indicate the filter, which rows actually get filtered, the actual formula and its value. IOW, so we can recreate what you have with little more than copy/paste/

Peter Thornton

Tuesday, September 13, 2011 12:37 PM
• Peter,

I'm not sure how but, your formula just worked for me. Quite strange since it didn't work the first time.

Anyway's my doubt is cleared. Thanks again!

Justin

Tuesday, September 13, 2011 12:46 PM