Results 1 to 10 of 10

Thread: Counting with Hidden Row

  1. #1

    Counting with Hidden Row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    How about using

    =ROW()

  3. #3
    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?

  4. #4
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    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 10:44 AM.

  5. #5
    Quote Originally Posted by Kellen Stevens View Post
    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)

  6. #6
    Quote Originally Posted by Bob Phillips View Post
    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.

  7. #7
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    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.

  8. #8
    That is amazing. Thank you so much! totally worked. No problem whatsoever.

    Thank you everyone! work just got a lot easier

  9. #9
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Greetings from Firenze. Ciao

  10. #10
    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.
    Thanks,
    Ankit Kaul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •