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. 


Thread Tools  Display Modes 
#1




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




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 nonblank 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, WinXPPro "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




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 nonblanks 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




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 nonblanks 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




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 nonblanks 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




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 nonblanks 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? 
#7




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

Thread Tools  
Display Modes  


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 