# Numbering of rows in a filter

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?

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.

• I never heard of `AGGREGATE` before. Definitely going to use that now :) – benshepherd Oct 14 '15 at 10:39

@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.