+ Reply to Thread
Results 1 to 3 of 3

How do I count visible items in an auto filter?

  1. #1
    Registered User
    Join Date
    03-19-2008
    Posts
    28

    Question How do I count visible items in an auto filter?

    When using auto filters. Sorting by (1) Date to Date then by (2) Product ID, I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). That Works.

    But additionally I need to count ONLY the number of items in the visible column that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too.

    How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Which column are we talking about? Assuming column P did you try

    =SUBTOTAL(3,P10:P1000)

    That should work assuming the "blank" cells are truly blank. If they contain formulas that return a blank then try

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(P10,ROW(P10:P1000)-ROW(P10),0)),--(P10:P1000<>""))

  3. #3
    Registered User
    Join Date
    03-19-2008
    Posts
    28

    Smile Problem Solved

    Works great. Thank you very much.
    n2lectual

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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