Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    # of Uniques in Filtered List (2000)

    I use the following formula to get the number of unique Transaction IDs (in range TransIDs ) in an UN - filtered list:

    =SUMPRODUCT((TransIDs<>"")/COUNTIF(TransIDs,TransIDs&""))

    When I do an advanced filter of the list ,say on the CustomerID, I want the formula to return the number of Unique Trans IDs just for this customer.
    Say the Cust ID is 28, or is in range A1.

    I tried playing also with the Subtotal function, using 2 or 3 as the first argument of the function, but couldn't quite get the right result.

    Any help modifying the SUMPRODUCT formula or other means?

    Thanks

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

    Re: # of Uniques in Filtered List (2000)

    The following variation on the function from <post:=528,282>post 528,282</post:> will count only filtered items:

    Function CountUniqueFilteredValues(InputRange As Range) As Long
    Dim cl As Range, UniqueValues As New Collection
    Application.Volatile
    On Error Resume Next ' ignore any errors
    For Each cl In InputRange
    If cl.Height > 0 Then
    UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
    End If
    Next cl
    On Error GoTo 0
    CountUniqueFilteredValues = UniqueValues.Count
    End Function

    Use like this in a cell:

    =CountUniqueFilteredValues(TransIDs)

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: # of Uniques in Filtered List (2000)

    Brilliant (and tricky)
    Never thought of using the height property of the cell.
    My thinking, of course, has been on the value of the cell.

    I guess this falls in the category of : <font color=blue>"Think Of the box!"</font color=blue>

    Thanks

Posting Permissions

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