Counting with Hidden Row

**Kellen Stevens** · 2012-12-12, 07:12 PM

I have a basic formula I would like to apply to a data set. What was originally about 200 rows is boiled down to 60 or so after filtering it. I now want to add a column to the left that counts each row. I normally use the formula =A1+1, but that does not work because I have hidden cells.

Any help?

Thanks in advance,

Kellen

**Bob Phillips** · 2012-12-12, 11:26 PM

How about using

=ROW()

**Kellen Stevens** · 2012-12-13, 02:32 PM

Unfortunately that shows the row number, but doesnt count them the way i need.
For example the first five rows are
3
7
20
24
25
=row() assigns the same number to these rows, whereas I need it to read something like:
3 1
7 2
20 3
24 4
25 5

Know what I mean?

**Canapone** · 2012-12-15, 10:35 AM

Hi, it could help an helper column, if values are in column C (example) in D2 and along the column

=IF(C2<>"",SUBTOTAL(103,C$2:C2),"")

Hope it helps

**Bob Phillips** · 2012-12-15, 06:38 PM

Originally Posted by Kellen Stevens

Unfortunately that shows the row number, but doesnt count them the way i need.
For example the first five rows are
3
7
20
24
25
=row() assigns the same number to these rows, whereas I need it to read something like:
3 1
7 2
20 3
24 4
25 5

Know what I mean?

No I don't, ROW() will NOT assign the same number, it will reflect the row's number, so it will show 1,2,3,... assuming the data starts in row 1. If it starts elsewhere, such as row 2, use this variation

=ROW()-ROW($A$1)

**Kellen Stevens** · 2012-12-18, 06:17 PM

Originally Posted by Bob Phillips

No I don't, ROW() will NOT assign the same number, it will reflect the row's number, so it will show 1,2,3,... assuming the data starts in row 1. If it starts elsewhere, such as row 2, use this variation

=ROW()-ROW($A$1)

I'm really sorry my excel knowledge is so inadequate that I cannot even understand your suggestion. So maybe I can refine my question for you:

My data starts in row 14. It ends in row 3876. It stretches about 20 columns. I use a filter to search for specific criteria - in this instance, every row that lists "Wellington" in column G as an institution. This totals roughly 600 rows. But I want an exact count. And I want to have number 1,2,3...next to each filtered row in Column A so it is easy to find.

The first row is 14. Then 15, 16, 17, 23, 34, 40, 47....this goes on 600 times.

So I added a column "A", where i want those numbers 1, 2, 3 to reside. What now?

Thank you both for your help. I really do appreciate it.

**Canapone** · 2012-12-18, 06:27 PM

Hi All, just try to copy in A14 and then copy the formula along your data (still not filtered: excuse my English).

=IF(g14<>"",SUBTOTAL(103,g$14:g14),"")
Then try to filter: hope it's a little help.