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

  • Thread starter Thread starter Tricia
  • Start date Start date
T

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!
 
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
 
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
 
For some reason, the last row doesn't filter properly when using my
formula.

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

works, however. Strange....

Bernie
 
Bernie,
For some reason, the last row doesn't filter properly when using my
formula.
Click to expand...
yes really strange, but can overcome by excluding the formula column from
the autofilter range.
Cecil
 
Back
Top