How about using
=ROW()
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 by Canapone; 2012-12-15 at 11:44 AM.
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
Greetings from Firenze. Ciao
As far as I think using subtotal function is the best way to deal with this situation.
You can try something likeUsing operational code as 103 will ignore the hidden cells while counting.Code:=subtotal(103, Range)
Thanks,
Ankit Kaul
Bookmarks