# Thread: Count column in Filtered Table

1. ## Count column in Filtered Table

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.

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. Please post a sample sheet. Images are useless to work with

3. 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. Originally Posted by Pecoflyer
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

5. 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. Originally Posted by NoS
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. or
=MOD(SUBTOTAL(3,E\$8:E8)-1,25)+1

8. Yah, that works great also.
Thanks for the replies.