Visible row index after applying a filter in Excel

[Hasmet Akgun]

Does anyone know an excel function that will give the index of the
visible row after filtering or sorting a table? Say, I have a table like
this:

type time place
c 12:20 1
f 12:40 2
c 13:30 3
f 14:20 4

Table is sorted with respect to time, and place refers to the index of
each row in this order. Now, if I filter by type 'f', the table would
still be sorted, but would have only two visible rows. In such a case,
I'd like the place row to be like [#NA, 1, #NA, 2], reflecting that rows
1 and 3 are invisible, first place is row #2 (but is now visible row
#1), and second place is row #4.

 

[Héctor Miguel]

hi, Hasmet !

assuming place in column "C" and data begins in row2, try this starting formula:

[C2] =subtotal(3,a$1:a2)-1

and copy/drag/... down (as needed)

hth,
hector.

__ OP __