Need a number count of items on list after Filterings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Taylor - 21 Mar 2006 00:16 GMT

I am using Excel 2003 to manage a Club list and would like an automatic
number count of the names on my list both before and after using various
Filter functions.

Reply to this Message

Richard Buttrey - 21 Mar 2006 00:52 GMT

>I am using Excel 2003 to manage a Club list and would like an automatic
>number count of the names on my list both before and after using various
[quoted text clipped - 8 lines]
>
>http://www.microsoft.com/office/community/en-us/default.mspx?mid=cb20ec89-8581-4
80c-bdb5-50936c5f4842&dg=microsoft.public.excel.worksheet.functions

Before is just

=COUNTA(A10:A20)

After is

=SUBTOTAL(2,A10:A20)

where A10:A20 is the column range which includes all the rows of your
data.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Taylor - 21 Mar 2006 01:36 GMT

I want Microsoft to make this feature automatic. If not then I will carry on
as I have. Place the number 1 in the top cell of a blank column of the list,
point to the lower corner of that top cell to activate Autofill and hold down
left mouse and drag the Autofill down the column to the end of the list. Sum
the list and voila! Taylor

> I am using Excel 2003 to manage a Club list and would like an automatic
> number count of the names on my list both before and after using various
[quoted text clipped - 8 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=cb20ec89-8581-4
80c-bdb5-50936c5f4842&dg=microsoft.public.excel.worksheet.functions

Reply to this Message

JMB - 21 Mar 2006 04:24 GMT

Are you using the Autofilter? Not sure about XL2003, but XL2000 shows a
message in the status bar (lower left) showing the number of records found
and Total number of records after performing a filter operation.

> I am using Excel 2003 to manage a Club list and would like an automatic
> number count of the names on my list both before and after using various
[quoted text clipped - 8 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=cb20ec89-8581-4
80c-bdb5-50936c5f4842&dg=microsoft.public.excel.worksheet.functions

Reply to this Message

George - 21 Mar 2006 06:16 GMT

=SUBTOTAL(function_num,ref1,ref2,...)
Refer to the help

Subtotal works with filtered lists
from the help file function_num can be the following
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

In your case you need 2 for count
Hope it helps

George

> I am using Excel 2003 to manage a Club list and would like an automatic
> number count of the names on my list both before and after using various
[quoted text clipped - 8 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=cb20ec89-8581-4
80c-bdb5-50936c5f4842&dg=microsoft.public.excel.worksheet.functions

Reply to this Message

Aladin Akyurek - 21 Mar 2006 07:32 GMT

Select the range of interest. Run Data|List|Create List. This provides
the AutoFilter functionality automatically. Activate the Total row to
obtain the automatic count you desire.

> I am using Excel 2003 to manage a Club list and would like an automatic
> number count of the names on my list both before and after using various
[quoted text clipped - 8 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=cb20ec89-8581-4
80c-bdb5-50936c5f4842&dg=microsoft.public.excel.worksheet.functions

Reply to this Message