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? |
#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 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
|
|||
|
|||
How do I count rows in a filtered list when using AutoFilter?
|
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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 | |
|
|
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 19th, 2003 12:30 AM |