Count unique values in filtered list
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Count unique values in filtered list

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count unique values in filtered list

     
    Is it possible to count the number of unique values in a list that is filtered without using a helpcolumn? I guess I need to use the subtotals in some way.

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    1,076
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count unique values in filtered list

    try this:
    and name the range a10:a16 data

    Sheet2

    A
    101000000006
    111000000006
    121000000006
    131000000007
    141000000008
    151000000008
    161000000008
    17
    183

    Spreadsheet Formulas
    CellFormula
    A18{=COUNTIF(data,data)}
    Formula Array:
    Produce enclosing { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


    martin

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    17,894
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count unique values in filtered list

    Try...

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly.

  4. #4
    New Member
    Join Date
    Jun 2009
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count unique values in filtered list

    Fantastic!!

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count unique values in filtered list

      
    Quote Originally Posted by Domenic View Post
    Try...

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly.
    Domenic,
    I was searching for a similar solution, and the one you posted worked perfectly for me.

    My needs were to have a unique task count on colum C data starting at row 7, that is where my autofilters were set. So, as filters are set on other colums I am getting the correct unique value counts based on those filters.
    Thank you!
    Lloyd

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com