Counting lines when using a filter
 

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

How about using SUBTOTAL function

--
Don Guillett
SalesAid Software

"hagan" wrote in message
...
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

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


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384605

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

"Don Guillett" wrote:

How about using SUBTOTAL function

--
Don Guillett
SalesAid Software

"hagan" wrote in message
...
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

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!

In article ,
"hagan" wrote:

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

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

On Tue, 5 Jul 2005 09:35:06 -0700, "hagan"
wrote:

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

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

"Domenic" wrote:

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!

In article ,
"hagan" wrote:

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

Olasa,

That works!! Thank you for your help.

Hagan

"olasa" wrote:


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


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384605

Gord,

That it!! Thanks for your help.

Hagan

"Gord Dibben" wrote:

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

On Tue, 5 Jul 2005 09:35:06 -0700, "hagan"
wrote:

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