Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

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

  1. #11
    New Member
    Join Date
    Aug 2010
    Posts
    2
    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 ?

    Hi, further to this thread from some time ago, I have an additional requirement of a formula based on the one issued by Aladin Akyurek =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

    I want to know how many times, any values in a range are repeated a certain number of times. I'm going round in circles trying to solve this.

    For example, in a column of cells containing say 1,1,1,1,2,2,2,2,3,3,3,3,4,4,5,5,6,6,6 then the formula above will return 6, as there are 6 unique values. What I want to do is know how many any values appear say 4 times (the reply would be 3, as 1,2 and 3 all appear 4 times). I realise a pivot table would be the easiest way of achieving this, but I really would like to accomplish it via formula instead.

    Many thanks in advance!

  2. #12
    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 chiefcrazybull View Post
    Hi, further to this thread from some time ago, I have an additional requirement of a formula based on the one issued by Aladin Akyurek =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

    I want to know how many times, any values in a range are repeated a certain number of times. I'm going round in circles trying to solve this.

    For example, in a column of cells containing say 1,1,1,1,2,2,2,2,3,3,3,3,4,4,5,5,6,6,6 then the formula above will return 6, as there are 6 unique values. What I want to do is know how many any values appear say 4 times (the reply would be 3, as 1,2 and 3 all appear 4 times). I realise a pivot table would be the easiest way of achieving this, but I really would like to accomplish it via formula instead.

    Many thanks in advance!
    Let A2:A20 house the example that you mention and C2 4, the occurrence frequency that you require...

    D2, control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(A2:A20<>"",MATCH("~"&A2:A20,A2:A20&"",0)),ROW(A2:A20)-ROW(A2)+1)=C2,1))
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #13
    New Member
    Join Date
    Aug 2010
    Posts
    2
    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, that works perfectly.

    I wont even try to pretend I understood most of that formula, but it works!

  4. #14
    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 chiefcrazybull View Post
    Thanks, that works perfectly.

    I wont even try to pretend I understood most of that formula, but it works!
    Although it does not provide explanations how the relevant formulas work, the following link might interest you:

    http://www.mrexcel.com/forum/showthread.php?t=292473
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    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 ?

    Hi,

    Is there a way to get this to work on non-contiguous cells (ie cell ranges that are not next to each other?)

    Thanks!

    Bolo

  6. #16
    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 bolo View Post
    Hi,

    Is there a way to get this to work on non-contiguous cells (ie cell ranges that are not next to each other?)

    Thanks!

    Bolo
    Hard to do. Do these cells/ranges have some regularity in lay-out, which could possibly be exploited?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    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 ?

    Quote Originally Posted by Aladin Akyurek View Post
    Hard to do. Do these cells/ranges have some regularity in lay-out, which could possibly be exploited?
    They do, but in the end i added a routine to make the ranges into one list first. But i was just wondering if it was possible using just one formula. I guess not! Thanks for your help.

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    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 ?

    Quote Originally Posted by Aladin Akyurek View Post
    You can also use the following formula to obtain the desired count of uniques items:

    =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))
    Hi Aladin,

    I know this thread is quite old, but i was wondering. What is the point of the if len part? THe formula seems to work ok without it in excel 2007. Is the if len part something needed to trapped an error in earlier versions of excel?

  9. #19
    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 bolo View Post
    Hi Aladin,

    I know this thread is quite old, but i was wondering. What is the point of the if len part? THe formula seems to work ok without it in excel 2007. Is the if len part something needed to trapped an error in earlier versions of excel?
    If any cell in A1:A6 is empty or house a formula blank:

    SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

    SUM(1/COUNTIF(A1:A6,A1:A6)) [ Original formula; Requires control+shift+enter ]

    both would yield a #DIV/0!

    To circumvent that issue and not to count an empty cell or a blank as a distinct item, you'd need...

    With the SumProduct version:

    SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))

    With the Sum Version:

    SUM(IF(LEN(A1:A6),1/COUNTIF(A1:A6,A1:A6))) [ Requires control+shift+enter ]

    of which the shorter version is:

    SUM(IF(A1:A6<>"",1/COUNTIF(A1:A6,A1:A6)))

    Here are some links with explanations...

    http://www.mrexcel.com/forum/showthread.php?t=36118

    http://www.mrexcel.com/forum/showthread.php?t=16682

    http://www.mrexcel.com/forum/showthread.php?t=70835

    Also, regarding efficiency issues...

    http://www.mrexcel.com/forum/showthread.php?t=292473
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    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 ?

    so the original formula was missing the outside sum!!! that is what confused me!

    Thanks for the explanations Aladin.

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
  •