Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Counting filtered rows (XL97, WinNT4)

    I have a user with a long spreadsheet who uses autofilter to find the rows he wants; can anyone tell me if there is an easy way for him to count how many rows result from using the autofilter?

    I tried the trick of typing 1 in a blank cell (clear of the data), 2 in the next one down, grabbing the handle and dragging down, but presumably the autofilter breaks this facility because it just put a 1 in every cell! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    I'm sure there must be a way, but I can't track it down!

    Any help would be gratefully appreciated!
    Beryl M


  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: Counting filtered rows (XL97, WinNT4)

    Use the SUBTOTAL() function, preferably on a column with numeric values.

    =SUBTOTAL(3,A1:A500), will count all the visible cells in the range A1:A500 of an autofiltered list.

    check out the Help files for other arguments, e.g. SUBTOTAL(9,A1:A500) will sum the visible cells.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Counting filtered rows (XL97, WinNT4)

    Andrew, that's great - thanks a million! I knew there had to be a way to do it but I just couldn't think what it was!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Posting Permissions

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