Counting with Hidden Row

Kellen Stevens

New member
Joined
Dec 12, 2012
Messages
4
Reaction score
0
Points
0
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
 
How about using

=ROW()
 
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?
 
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
 
Last edited:
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)
 
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.
 
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.
 
That is amazing. Thank you so much! totally worked. No problem whatsoever.

Thank you everyone! work just got a lot easier
 
As far as I think using subtotal function is the best way to deal with this situation.
You can try something like
Code:
=subtotal(103, Range)
Using operational code as 103 will ignore the hidden cells while counting.
 
Back
Top