Donate SIGN UP

Excel Question

Avatar Image
Chapel | 12:36 Mon 05th Sep 2005 | Technology
6 Answers

If I want to count the number of rows in a spreadsheet, I take the lowest number from the highest.

BUT - if I want to count the number of rows on a sheet which is using filters, I have to physically count, as the numbers are no longer sequential.

Is there a 'count' command of any sort which would do this for me?

Gravatar

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by Chapel. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Quickest way I can think of is to select the rows you want to count. Before you let go of the left mouse button, the little box that normally tells you the address of the cell you're in, will display the number of rows & columns you have selected, 9R x 2C for example.

If you've got loads of them you want to select, click the first one, then shift-click the last one and before you let go of the shift key, again, it'll tell you how many you've selected in the little box on the left near the top.
Question Author
Thanks Ralph - exactly what i needed

I do not think that method would work for a filtered list... it seems to count the hidden rows. (It certainly does in Excel 2000).

Likewise the formulae ROWS and COUNTA include hidden rows in the count.

I found the only way to counteract this was to copy the selected post-filtered range to a different part of the sheet and then use any of the above methods to give an accurate count.

If you are using a newer version of Excel that does not exhibit this behaviour then ignore this post.  

Question Author

** ****

You're right.

thanks anyway

To count rows of a filtered list, use this:

=SUBTOTAL(2,a1:a100)

Where A1:A100 is the range of your whole list, and "2" is the COUNT function.  If you then use autofilter, the value will change to reflect how many rows are actually visible.

A very handy function.

Nice one Aquariel.

1 to 6 of 6rss feed

Do you know the answer?

Excel Question

Answer Question >>