software

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?

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-Proquot;chiefcookquot; wrote:

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

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.

quot;chiefcookquot; wrote:

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

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.comquot;bpeltzerquot; wrote:

gt; If your filtered data (absent the header row) is in G2:G5, for example, then
gt; =SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells which
gt; passed the filter.
gt;
gt; quot;chiefcookquot; wrote:
gt;
gt; gt; I have a database list in Excel spreadsheet and use the AutoFilter to look at
gt; gt; selected items. How can I count the number of rows with data that are
gt; gt; displayed when the worksheet is filtered, similar to SUBTOTAL does when a
gt; gt; column has numbers in it?

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

quot;RichUEquot; gt; wrote in message
...
gt; I found this old thread using Excel Help Search. The SUBTOTAL function
gt; works,
gt; and I entered the formula directly below my data. It checks a single
gt; column
gt; of data. When no filter is applied, the formula result is 130. If I filter
gt; on
gt; Blanks in the column of interest, the result is zero. Why?
gt;
gt; I want to use this as a means of indicating progress (viz. towards all
gt; cells
gt; containing a value). But I would continually be using Show All to discover
gt; how many blanks remain. Is there a better way?
gt; --
gt; Richard
gt;
gt; Search the web and raise money for charity at www.everyclick.com
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt;gt; If your filtered data (absent the header row) is in G2:G5, for example,
gt;gt; then
gt;gt; =SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells
gt;gt; which
gt;gt; passed the filter.
gt;gt;
gt;gt; quot;chiefcookquot; wrote:
gt;gt;
gt;gt; gt; I have a database list in Excel spreadsheet and use the AutoFilter to
gt;gt; gt; look at
gt;gt; gt; selected items. How can I count the number of rows with data that are
gt;gt; gt; displayed when the worksheet is filtered, similar to SUBTOTAL does when
gt;gt; gt; a
gt;gt; gt; column has numbers in it? 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.comquot;Ashish Mathurquot; wrote:

gt; Hi,
gt;
gt; The SUBTOTAL(9,range) shoud work on the filtered data. I wonder why you get
gt; a 0 value. Could you share more details
gt;
gt; --
gt; Regards,
gt;
gt; Ashish Mathur
gt; Microsoft Excel MVP
gt; www.ashishmathur.com
gt;
gt; quot;RichUEquot; gt; wrote in message
gt; ...
gt; gt; I found this old thread using Excel Help Search. The SUBTOTAL function
gt; gt; works,
gt; gt; and I entered the formula directly below my data. It checks a single
gt; gt; column
gt; gt; of data. When no filter is applied, the formula result is 130. If I filter
gt; gt; on
gt; gt; Blanks in the column of interest, the result is zero. Why?
gt; gt;
gt; gt; I want to use this as a means of indicating progress (viz. towards all
gt; gt; cells
gt; gt; containing a value). But I would continually be using Show All to discover
gt; gt; how many blanks remain. Is there a better way?
gt; gt; --
gt; gt; Richard
gt; gt;
gt; gt; Search the web and raise money for charity at www.everyclick.com
gt; gt;
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt;gt; If your filtered data (absent the header row) is in G2:G5, for example,
gt; gt;gt; then
gt; gt;gt; =SUBTOTAL(3,G2:G5) will count the number of non-blanks in those cells
gt; gt;gt; which
gt; gt;gt; passed the filter.
gt; gt;gt;
gt; gt;gt; quot;chiefcookquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; I have a database list in Excel spreadsheet and use the AutoFilter to
gt; gt;gt; gt; look at
gt; gt;gt; gt; selected items. How can I count the number of rows with data that are
gt; gt;gt; gt; displayed when the worksheet is filtered, similar to SUBTOTAL does when
gt; gt;gt; gt; a
gt; gt;gt; gt; column has numbers in it?
gt;

Thanks RON

its help me alot, quot;Ron Coderrequot; wrote:

gt; Try this:
gt; Use 3 for the first argument in the SUBTOTAL function....it counts non-blank
gt; cells in the filtered list. Remember to either skip the header row or
gt; subtract 1 from the formula result.
gt;
gt; Example:
gt; =SUBTOTAL(3,A2:A10)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;chiefcookquot; wrote:
gt;
gt; gt; I have a database list in Excel spreadsheet and use the AutoFilter to look at
gt; gt; selected items. How can I count the number of rows with data that are
gt; gt; displayed when the worksheet is filtered, similar to SUBTOTAL does when a
gt; gt; column has numbers in it?