Counting lines when using a filter

H

hagan

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
 
H

hagan

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
 
D

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!
 
G

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
 
H

hagan

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
 
H

hagan

Gord,

That it!! Thanks for your help.

Hagan

Gord Dibben said:
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
Click to expand...
 
Top