+ Reply to Thread
Results 1 to 5 of 5

Count visible autofilter results

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    Excel, Word, Outlook, OneNote 2013
    Posts
    154

    Count visible autofilter results

    I can do a countifs to get the number of cells matching a string.
    I can filter on a different column and the countifs doesn't change.
    I would like to autofilter on a second column and have the countifs show the count on the visible cells only.

    Essentially it is a personnel file and I am looking at jobs. I would like to filter on status (Active, Terminated, On Leave, etc) and the countifs of adjacent (not in the table) two cells tell me how many of the visible cells are "Trainee" or "Tech". I know I could just apply all three autofilters and then note the results. But, I am not the only one using this spreadsheet and the other person is kind of phobic.

    I thought I had a lead using the subtotal function. But I just can't seem to make it work.

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Count visible autofilter results

    Check out the function "aggregate()" with 3 and 1 as the first 2 parameters: aggregate(3, 1, . . .
    This will count visible cells only.

    EDIT: DISREGARD THIS - SEE POST #3
    Last edited by GeoffW283; 07-21-2020 at 12:18 AM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Count visible autofilter results

    Sorry, not so easy after all

    This may help though.

    mr_phil.png

    Col-A contains the list. Cell D2 contains the value that the col-A list is to be matched against.

    Col-B is the filter. In the above picture rows 5 and 6 are filtered out (A5 contains "bb")

    E9 contains the count of visible col-A rows that match "bb" = 2 In E9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Workbook is attached.

    Hopefully you can adapt this to your needs.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    Excel, Word, Outlook, OneNote 2013
    Posts
    154

    Re: Count visible autofilter results

    Quote Originally Posted by GeoffW283 View Post
    Sorry, not so easy after all

    This may help though.

    Attachment 687594

    Col-A contains the list. Cell D2 contains the value that the col-A list is to be matched against.

    Col-B is the filter. In the above picture rows 5 and 6 are filtered out (A5 contains "bb")

    E9 contains the count of visible col-A rows that match "bb" = 2 In E9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Workbook is attached.

    Hopefully you can adapt this to your needs.
    Thank you very much. That will work. I had an epiphany while driving home today. I rebuilt the countifs to include a row height greater than 0 as one of the criteria. That also works. I am going to put both solutions onto the workiing sheet to see if various tasks interferes with one but not the other....

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Count visible autofilter results

    I had an epiphany while driving home today.
    Always the way
    Hopefully one or other of the options works for you.
    Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count if unique values in range in visible cellls only (after autofilter)
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-09-2015, 04:42 PM
  2. autofilter, visible rows count error
    By florin_excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2013, 11:22 AM
  3. Use visible rows from autofilter to build Pivot table. Or use visible rows to Copy/Paste
    By mwhitedesigns in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 10:34 AM
  4. [SOLVED] Copy/Pasting Values from visible cells (using autofilter) to visible cells
    By evakotsi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2012, 07:49 AM
  5. [SOLVED] After autofilter my results are not visible..
    By Darin Kramer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2006, 01:49 PM
  6. [SOLVED] with Autofilter on, display count results in lower left corner wit
    By jsky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2005, 06:05 PM
  7. Autofilter - display column showing visible row count
    By rgarber50 in forum Excel General
    Replies: 5
    Last Post: 07-26-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1