Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    counting records (Office 2003 SP 2)

    hi everyone;
    i am sure the answer is simple, but i have not been able to solve it.
    i have a table (a reduced example attached, the database i'm dealing is much larger).
    i will put a data filter to select, for instance, all instances ZV in first column. and i want to have (i some cell, somewhere), an AUTOMATICcount of how many they are.
    then, changing the filter criteria (to Zn, for instance), i want that cell displaying the total count of those instances automatically updated.
    is it possible? i tried the functions in database, but found they didn't apply to this case (or, very likely, perhaps i didn't know how to use them).
    thanks a lot in advance

    danielr

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting records (Office 2003 SP 2)

    You can use the SUBTOTAL function with 3 as first argument (3 represents COUNTA, i.e. the number of non-blank values). SUBTOTAL takes filtering into account.

    In order to apply a filter, you need to insert a first row with field names (column headers), so the data will begin in row 2. The following formula will count the number of filtered records:

    =SUBTOTAL(3,A2:A33)

    See attached version (I applied an AutoFilter)

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: counting records (Office 2003 SP 2)

    Thank you very much HANS!!!!
    I'll try it and let you know.

    A million thnx again.

    danielr.

Posting Permissions

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