# Thread: Excel Filtered List (Excel 2000)

1. ## Excel Filtered List (Excel 2000)

The answer to my question should be simple, but I can not find it. I have a list of names (and other stuff) which I "filter" to get specific people's data. I just want to count the number of times this person shows up when I filter the list with this person's name. I have tried count, counta, sum, sumif, etc...can not find one that will do this. Any help ?
Thanks

2. ## Re: Excel Filtered List (Excel 2000)

Use the SUBTOTAL() Function.

=SUBTOTAL(3,A1:A100)

will count (COUNTA) the number of visible items in the Range(A1:A100) after it is filtered. Place the function outside (beneath) the date range, and each time you apply a filter it should update. The first argument, 3 in this case, determines the function applied. 3 = COUNTA(), 9 = SUM() etc. Check help for more details.

Andrew C

3. ## Re: Excel Filtered List (Excel 2000)

Thank you Andrew. That is exactly what I was looking for. I have applied the formula and it works great.
Maybe I can pass another one to you ? In this same data sheet, the dates come in as: 20010204 (feb 4, 2001) all in a line like that. I wanted to filter also using dates only after (or before) certain dates. I know I may have to convert the dates into another format (maybe a macro ?) before using the filtered totals.
Thanks, Tim

4. ## Re: Excel Filtered List (Excel 2000)

Hi Tim,
Actually I can't see any reason to convert those dates for filtering purposes - you can just use < or > according to what you want. I often name my file versions that way so that I can easily get them in date order in Explorer.
Hope that helps.

5. ## Re: Excel Filtered List (Excel 2000)

Filtering on actual date values is tricky, and you must use a macro. I think Rory is right, use the text version you have. You could split it into 3 seperate fields if that is more convenient,

the year = LEFT(A1,4), the month = MID(A1,5,2), and the Day = Right(A1,2), where A1 holds the date text, say 20010204

Andrew C

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•