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

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)


__ OP __

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question