A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2005, 01:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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?
Ads
  #2  
Old December 22nd, 2005, 02:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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?

  #3  
Old December 22nd, 2005, 02:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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?

  #4  
Old October 21st, 2008, 02:00 PM posted to microsoft.public.excel.worksheet.functions
RichUE
external usenet poster
 
Posts: 74
Default 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?

  #5  
Old October 21st, 2008, 02:07 PM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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?


  #6  
Old October 21st, 2008, 04:10 PM posted to microsoft.public.excel.worksheet.functions
RichUE
external usenet poster
 
Posts: 74
Default 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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Count field to recognise rows with negative values in Exc. hamish Worksheet Functions 2 June 20th, 2005 05:06 AM
filtering a list box using a filtered combo box davidchadwick New Users 3 May 27th, 2005 11:32 PM
How do I get Excel to count identical rows in a list? Ali Dennis General Discussion 1 October 28th, 2004 04:05 PM
Adding new rows to a list when Protection is on Rolando Worksheet Functions 0 May 5th, 2004 01:46 AM
Counting Unique Elements of filtered list with Blank Cells Peo Sjoblom Worksheet Functions 3 December 18th, 2003 11:30 PM


All times are GMT +1. The time now is 12:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.