Numbering of rows in a filter

Question

In a range with filter, I want to have a column of row numbers which will change according to the filter, so that rows always count consecutively from 1. For example, the following is a range with filter:

Number   Name    Gender
1        Alice   F
2        Jason   M
3        Ka      F
4        Fiona   F
5        Albert  M

Now suppose we apply a filter to show rows where Gender is M:

Number   Name    Gender
2        Jason   M
5        Albert  M

The above is what Excel normally shows, but I want the Number to count from 1 consecutively like this:

Number   Name    Gender
1        Jason   M
2        Albert  M

So, the rows should be renumbered according to the filter. I have tried something like =MAX(...)+1 or using the SUBTOTAL() function, but I haven't succeeded yet. Is it possible to write a formula for the Number column to do this task? How?

Answer 1

Use this formula:

=AGGREGATE(3,5,$A$1:A1)

into cell A2.  (This assumes that you have a column heading in cell A1.)

Parameters of AGGREGATE():

• Function_num = 3, Count all non-blank cells in a range
• Options = 5, ignore hidden rows in the range
• Array = $A$1:A1, range from first row to the row above selected cell

Sample output:

As mentioned by benshepherd, SUBTOTAL() can be used too.

Answer 2

@Máté Juhász just got there ahead of me. I was going for an alternative approach using SUBTOTAL. In A2, put =SUBTOTAL(103,B$2:B2) and fill down.

The SUBTOTAL function performs a numbered function on its arguments - these functions are described in the Help. 103 corresponds to COUNTA ignoring hidden rows. (You'd use a value of 3 to include hidden rows.) COUNTA counts the number of non-blank cells in a range. We use the B$2:B2 syntax to keep the top cell the same, and expand the range as we go down the list.