Counting lines when using a filter

[Guest]

After using a filter to view certain rows of a spreadsheet, is there a way to
get an accurate count of how many rows are shown by either clicking and
dragging the mouse over the selection or clicking on the first row and then
shift-click on the last row?

Thanks in advance,
Hagan

 

[Don Guillett]

How about using SUBTOTAL function

 

[olasa]

Right click on the bottom status bar, and select Count
See encl. picture:
http://www.excelforum.com/attachment.php?attachmentid=3569&stc=1
HTH
Ola


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3569 |
+-------------------------------------------------------------------+

 

[Guest]

Hi Don,

I tried the SUBTOTAL function but it seems to be better suited for using
this function on numbers in cells of a filtered spreadsheet, but I'm looking
for a way to count the number of rows shown after a document has been
filtered. None of my columns have numbers for me to use the SUBTOTAL
function with the COUNT or COUNTA options. Do you have any other
suggestions, or is there a way to tweak the SUBTOTAL function to count a text
string.

Hagan

 

[Domenic]

Try the following....

To count the number of cells that are not empty:

=SUBTOTAL(3,A2:A10)

To count the number of cells that contain a specific text string:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),0,1)),(
A2:A10="Text String")+0)

Hope this helps!

 

[Gord Dibben]

Just highlight the visible rows and right-click on the status bar and select
"count".

Won't count rows hidden by filter.


Gord Dibben Excel MVP

 

[Guest]

Domenic,

Worked like a charm...a little trickier than what I was looking for, but
it worked. However, the 2 replies after yours were a little simpler.

Thanks,
Hagan

 

[Guest]

Olasa,

That works!! Thank you for your help.

Hagan

 

[Guest]

Gord,

That it!! Thanks for your help.

Hagan

Just highlight the visible rows and right-click on the status bar and select
"count".

Won't count rows hidden by filter.


Gord Dibben Excel MVP