Count column in Filtered Table

szchris384

New member
Joined
Mar 2, 2014
Messages
8
Reaction score
0
Points
0
Excel Version(s)
16.0.12827.20235 64bit
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.
Capture.JPG


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

Attachments

  • Count 25 example.xlsx
    131.8 KB · Views: 11
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))
 
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
 
or
=MOD(SUBTOTAL(3,E$8:E8)-1,25)+1
 
Yah, that works great also.
Thanks for the replies.
 
Back
Top