Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: How do you Count the number of unique values in a list ?

  1. #21
    New Member
    Join Date
    Apr 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Hello there,

    I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

    I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

    Thanks in advance!!

    Jasper

  2. #22
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Quote Originally Posted by thejazz View Post
    Hello there,

    I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

    I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

    Thanks in advance!!

    Jasper
    Control+shift+enter, not just enter:
    Code:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E5,ROW(E5:E22)-ROW(E5),,1)),
         IF(E5:E22<>"",MATCH("~"&E5:E22,E5:E22&"",0))),ROW(E5:E22)-ROW(E5)+1),1))
    which does effect a distinct count of the items in E5:E22, a range within an autofiltered area of data.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #23
    New Member
    Join Date
    Apr 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Thanks Aladin,

    I've tried your formula but for me (I use plain Excel 2010, no ad-ons) the formula doesn't seem to work. My Excel doesn't recognize FREQUENCY, any other solutions/suggestions?
    Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.

  4. #24
    New Member
    Join Date
    Apr 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: How do you Count the number of unique values in a list ?

    Thanks a lot Aladin, It works like a charm.

    So other readers: Ignorem my last comment
    Quote Originally Posted by thejazz View Post
    Thanks Aladin,

    I've tried your formula but for me (I use plain Excel 2010, no ad-ons) the formula doesn't seem to work. My Excel doesn't recognize FREQUENCY, any other solutions/suggestions?
    Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.
    My mistake!

  5. #25
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Quote Originally Posted by thejazz View Post
    Thanks a lot Aladin, It works like a charm.

    So other readers: Ignorem my last comment

    My mistake!
    Glad to hear you sorted it out. Thanks for providing feedback.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #26
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Quote Originally Posted by thejazz View Post
    Hello there,

    I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

    I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

    Thanks in advance!!

    Jasper
    This array formula** will count the unique entries in the filtered or unfiltered range A2:A15...

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A15)-ROW(A2),0)),MATCH(A2:A15,A2:A15,0)),ROW(A2:A15)-ROW(A2)+1),1))

    ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

    Assumes no empty cells within the range to be counted.
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

Some videos you may like

User Tag List

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
  •