How do I count rows in a filtered list when using AutoFilter?
 

I have a database list in Excel spreadsheet and use the AutoFilter to look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

Try this:
Use 3 for the first argument in the SUBTOTAL function....it counts non-blank
cells in the filtered list. Remember to either skip the header row or
subtract 1 from the formula result.

Example:
=SUBTOTAL(3,A2:A10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

If your filtered data (absent the header row) is in G2:G5, for example, then
=SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells which
passed the filter.

"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

I found this old thread using Excel Help Search. The SUBTOTAL function works,
and I entered the formula directly below my data. It checks a single column
of data. When no filter is applied, the formula result is 130. If I filter on
Blanks in the column of interest, the result is zero. Why?

I want to use this as a means of indicating progress (viz. towards all cells
containing a value). But I would continually be using Show All to discover
how many blanks remain. Is there a better way?
--
Richard

Search the web and raise money for charity at www.everyclick.com


"bpeltzer" wrote:

If your filtered data (absent the header row) is in G2:G5, for example, then
=SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells which
passed the filter.

"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

Hi,

The SUBTOTAL(9,range) shoud work on the filtered data. I wonder why you get
a 0 value. Could you share more details

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RichUE" wrote in message
...
I found this old thread using Excel Help Search. The SUBTOTAL function
works,
and I entered the formula directly below my data. It checks a single
column
of data. When no filter is applied, the formula result is 130. If I filter
on
Blanks in the column of interest, the result is zero. Why?

I want to use this as a means of indicating progress (viz. towards all
cells
containing a value). But I would continually be using Show All to discover
how many blanks remain. Is there a better way?
--
Richard

Search the web and raise money for charity at www.everyclick.com


"bpeltzer" wrote:

If your filtered data (absent the header row) is in G2:G5, for example,
then
=SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells
which
passed the filter.

"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to
look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when
a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

I'm using COUNTBLANK now as it seems more reliable.

On the same column of unfiltered data:
=SUBTOTAL(109, ...) gives 0
=SUBTOTAL(103, ...) gives 130
=COUNTBLANK(...) gives 45.
--
Richard

Search the web and raise money for charity at www.everyclick.com


"Ashish Mathur" wrote:

Hi,

The SUBTOTAL(9,range) shoud work on the filtered data. I wonder why you get
a 0 value. Could you share more details

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RichUE" wrote in message
...
I found this old thread using Excel Help Search. The SUBTOTAL function
works,
and I entered the formula directly below my data. It checks a single
column
of data. When no filter is applied, the formula result is 130. If I filter
on
Blanks in the column of interest, the result is zero. Why?

I want to use this as a means of indicating progress (viz. towards all
cells
containing a value). But I would continually be using Show All to discover
how many blanks remain. Is there a better way?
--
Richard

Search the web and raise money for charity at www.everyclick.com


"bpeltzer" wrote:

If your filtered data (absent the header row) is in G2:G5, for example,
then
=SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells
which
passed the filter.

"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to
look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when
a
column has numbers in it?

How do I count rows in a filtered list when using AutoFilter?
 

Thanks RON

its help me alot,





"Ron Coderre" wrote:

Try this:
Use 3 for the first argument in the SUBTOTAL function....it counts non-blank
cells in the filtered list. Remember to either skip the header row or
subtract 1 from the formula result.

Example:
=SUBTOTAL(3,A2:A10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"chiefcook" wrote:

I have a database list in Excel spreadsheet and use the AutoFilter to look at
selected items. How can I count the number of rows with data that are
displayed when the worksheet is filtered, similar to SUBTOTAL does when a
column has numbers in it?