Numbering Rows (1, 2, 3) Within A Filter

[Tricia]

Does anyone know how I can number each row (1, 2, 3, etc)
included in a filter? I need it to be dynamic, depending
on the column filter selected. The first row would not
always be the same - it would depend on the filter
selected. But it should always be 1, regardless of the
contents.

For example, the first row should always be 1, even if the
the first worksheet row number is 25 for the filter
criteria selected.

Thanks!

 

[David Hager]

Interesting!

For this example, the table headings are in row 3. The first column (A) will
contain the formula shown below. Thus, the main table data will be in Column
B thru whatever.

Put this formula in A4. Hold down the Ctrl and Shift keys when entering this
formula. This, fill the formula dowm.

=SUM(SUBTOTAL(3,OFFSET($B$4:B4,ROW($B$4:B4)-MIN(ROW($B$4:B4)),,1)))

When the filter is applied, Column A will contain sequential numbers from 1
to whatever for the filtered rows.

Thanks for the challenge,
David Hager
Excel FMVP

 

[Cecilkumara Fernando]

Tricia,
I think Debra posted this solution sometime back
assuming that your index column is ColumnA
and each row in ColumnB of the range has data
instead of just putting 1,2,3 put this formula in B2
=SUBTOTAL(3,$B$2:B2)
and fill down
the index will adjust with the filter
HTH
Cecil

 

[Bernie Deitrick]

David,

Why not just

=SUBTOTAL(3,$B$4:B4)

Bernie

 

[Bernie Deitrick]

For some reason, the last row doesn't filter properly when using my
formula.

=SUM(SUBTOTAL(3,$B$4:B4))

works, however. Strange....

Bernie

 

[Cecilkumara Fernando]

Bernie,

For some reason, the last row doesn't filter properly when using my
formula.

yes really strange, but can overcome by excluding the formula column from
the autofilter range.
Cecil