Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic count from AutoFilter (2000/2002)

    I have an autofiltered datalist with, below it, a cell using DCOUNT to count the filtered set of records. At present it is a two-step process: selecting the filter criteria then selecting the same criteria for the DCOUNT. Is there a way to pass the selection from the autofilter drop-down directly to cell used for the DCOUNT criteria?

    Any help gratefully received.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatic count from AutoFilter (2000/2002)

    Instead of using DCOUNT, use the Subtotal function:

    SUBTOTAL(2,C1:C100) will generate the count of "filtered" cells in C1:C100
    SUBTOTAL(9,C1:C100) will generate the sum of the filtered cells C1:C100

    The first parameter is:
    <pre>1 AVERAGE
    2 COUNT
    3 COUNTA
    4 MAX
    5 MIN
    6 PRODUCT
    7 STDEV
    8 STDEVP
    9 SUM
    10 VAR
    11 VARP</pre>


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic count from AutoFilter (2000/2002)

    Steve:

    I've used the Data/Subtotals command before, but not the SUBTOTAL function.

    Just what I wanted. Many thanks.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic count from AutoFilter (2000/2002)

    Choosing a cell in an spare column somewhere and clicking the autosum button then selecting the column containing the filtered list returns the subtotal in 2 clicks.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic count from AutoFilter (2000/2002)

    Thanks John, but I wanted the count total to change automatically when another filter was chosen, which is what the SUBTOTAL function does.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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