Results 1 to 8 of 8

Thread: Count column in Filtered Table

  1. #1
    Seeker szchris384's Avatar
    Join Date
    Mar 2014
    Posts
    8
    Articles
    0
    Excel Version
    14.0.7212.5000 32bit

    Count column in Filtered Table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I'm needing help with creating a counter column within a table that has a filter(s).
    The counter needs to reset after 25 back to 1.
    Essentially creating a counter system that goes from 1-25, 1-25 1-25, etc. on the filtered table.
    If the filters are not active, the formula is straight forward.
    =IF(INDIRECT("B"&ROW()-1)="25","01", TEXT(INDIRECT("B"&ROW()-1)+1,"0#"))

    If the filters are active the counts include the filtered (hidden) rows. Resulting in 17, 19, 19, 25, 09, 19, etc.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	43.3 KB 
ID:	8517


    With the filter active, I can use the Subtotal function to get a counter system skipping the filtered out items.
    =SUBTOTAL(3,C$8:C8) (table starts on Row 8) (col C is text and always has a value in it)
    But this will do the whole table from 1-last entry (that meet the filter criteria)
    I would like the counter to go from 1-25, then 1-25, etc. on the filtered table

    Any help would be appreciated

    400+ records in the table
    several different filter options
    VBA is not an option.

    Thanks
    Chris

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,667
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet. Images are useless to work with
    Thank you Ken for this secure forum.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    VBA is not an option.
    Mind if I ask why ?
    I don't think you can determine visibility with a formula.
    The picture you've attached has buttons on it, they require VBA.

  4. #4
    Seeker szchris384's Avatar
    Join Date
    Mar 2014
    Posts
    8
    Articles
    0
    Excel Version
    14.0.7212.5000 32bit
    Quote Originally Posted by Pecoflyer View Post
    Please post a sample sheet. Images are useless to work with
    Here is a sample sheet of what I'm trying to accomplish.

    Thanks for the help.
    Chris
    Attached Files Attached Files

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Does this work ?
    In Non Filtered sheet, cell C8
    =IF(MOD(SUBTOTAL(3,E$8:E8),25)=0,25,MOD(SUBTOTAL(3,E$8:E8),25))

  6. #6
    Seeker szchris384's Avatar
    Join Date
    Mar 2014
    Posts
    8
    Articles
    0
    Excel Version
    14.0.7212.5000 32bit
    Quote Originally Posted by NoS View Post
    Does this work ?
    In Non Filtered sheet, cell C8
    =IF(MOD(SUBTOTAL(3,E$8:E8),25)=0,25,MOD(SUBTOTAL(3,E$8:E8),25))


    Thank You, works perfectly!
    Chris

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    or
    =MOD(SUBTOTAL(3,E$8:E8)-1,25)+1

  8. #8
    Seeker szchris384's Avatar
    Join Date
    Mar 2014
    Posts
    8
    Articles
    0
    Excel Version
    14.0.7212.5000 32bit
    Yah, that works great also.
    Thanks for the replies.

Tags for this Thread

Posting Permissions

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