Excel Question

[Chapel]

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?

[Ralph]

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.

[Chapel]

Thanks Ralph - exactly what i needed

[kempie]

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.  

[Chapel]

** ****

You're right.

thanks anyway

[Aquariel]

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.

[kempie]

A very handy function.

Nice one Aquariel.