How do I count rows in a filtered list when using AutoFilter? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              chiefcook 12/22/2005 5:41 AM PST   Question   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?     Was this post helpful to you?         Reply |   Top           Ron Coderre 12/22/2005 6:04 AM PST   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?     Was this post helpful to you?         Reply |   Top           Sohail 11/10/2008 1:20 AM PST   Thanks RON its help me alot, sam_atd@hotmail.com "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?     Was this post helpful to you?         Reply |   Top           bpeltzer 12/22/2005 6:05 AM PST   Answer   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?     Was this post helpful to you?         Reply |   Top           RichUE 10/21/2008 5:59 AM PST   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?     Was this post helpful to you?         Reply |   Top           Ashish Mathur  10/21/2008 6:08 AM PST   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" <RichUE@discussions.microsoft.com> wrote in message news:E8840652-0437-435F-84B1-007AAF22BA54@microsoft.com... > 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?     Was this post helpful to you?         Reply |   Top           RichUE 10/21/2008 8:10 AM PST   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" <RichUE@discussions.microsoft.com> wrote in message > news:E8840652-0437-435F-84B1-007AAF22BA54@microsoft.com... > > 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? >     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies