Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Pa, Pennsylvania, USA
    Posts
    122
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Pa, Pennsylvania, USA
    Posts
    122
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •