Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Count distinct function?...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count distinct function?...

    Hi there.

    Is there a way to count distinct values in a range of cells in a worksheet?

    i'm looking for something like a COUNT DISTINCT function that would count all distinct values in an entire column. any ideas?

    any help would be appreciated!

    Len

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    Du you mean count unique?
    (English is not my native tongue)


    Then try:

    =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
    "Fair Winds and Following Seas"

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    Fairwinds,
    Care to elaborate on the qriteria used in the countif arguments?
    It does work, but not sure about the intuition.
    Cheers.

  4. #4
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    Hi,
    I learned this formula from Aladin but I cannot find a post to refer to where he explains it.

    Anyway, The first part gives 1 for each cell that is not empty. Then divides it with how many instances there is of this value.
    Summing that gives you the "unique count".

    The &"" is to avoid 0 and #DIV/0 when a cell is empty.
    "Fair Winds and Following Seas"

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    So (as usual) everything begins with Aladin.
    Sould've guessed.

    Hopefully he reads in on this one and elaborates, since I
    cannot say I follow/agree with your explanation.
    Anyhow, to me it looks as though the second part (countif)
    returns number of occurences for the "non distinct".

    Waiting on Aladin.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count distinct function?...

    Quote Originally Posted by luckycharm
    So (as usual) everything begins with Aladin.
    Sould've guessed.

    Hopefully he reads in on this one and elaborates, since I
    cannot say I follow/agree with your explanation.
    Anyhow, to me it looks as though the second part (countif)
    returns number of occurences for the "non distinct".

    Waiting on Aladin.
    I did have two Invalid_Session in trying to respond to this one.

    Well, the formula Fairwinds posted is due to Harlan Grove, although I did play the role of an evaluator regarding the earlier public versions Harlan posted at the worksheet.functions.

    Harlan eventually succeeded to create a SumProduct version of David Hager's formula, which is:

    {=SUM(1/COUNTIF(Range,Range))}

    The kernel idea of this formula is:

    1/Tokens(Type)

    Tha is, if Bob is a distinct name (a type) and Bob occurs say 3 times, we can assign a weight to each of its occurrences (tokens), thus:

    1/3, 1/3, 1/3

    Summing the individual weights we get 1 back. That is: dividing 1 by the tokens, we get a distinct/unique/type count.

    Two things to note about this formula:

    If Range has any empty cell, we get #DIV/0! for COUNTIF(EmptyCell,EmptyCell) == 0.

    If Range has any cell housing a formula (like =IF(X1,1,"") that returns a blank (sometimes referred to as null string), COUNTIF(CellWithFormulaBlank,CellWithFormulaBlank), we get a count of 1 for such a cell.

    It's somewhat theoretical whether an empty cell should be considered a distinct type. The same holds for a formula-blank. Supposing that they are not distinct types, Hager's formula needs some modification before it can be applied to a Range housing empty cells or formula blanks:

    {=SUM(IF(LEN(Range),1/COUNTIF(Range,Range))}

    or

    {=SUM(IF(Range<>"",1/COUNTIF(Range,Range))}

    It's obvious that using the latter (with lesser function call) is better (for robustness).

    My contribution (along with Juan) consists of just this modification.

    How does this formula works using an example?

    Let A2:A8 house the following sample:

    {"Bob";"Bob";"Bob";"Jane";"Jane";EmptyCell;"Thomas"}

    where EmptyCell stands for an empty cell (not for a distinct type).

    The COUNTIF(A2:A8,A2:A8) bit gives:

    {3;3;3;2;2;0;1}

    The 1/COUNTIF(A2:A8,A2:A8) gives:

    {0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;#DIV/0!;1}

    Note #DIV/0!. Clearly, there are 3 types/distinct/unique items if SUM could ignore the error values.

    With

    {=SUM(IF(A2:A8<>"",1/COUNTIF(A2:A8,A2:A8)))}

    we have successively:

    =SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE},{0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;#DIV/0!;1}))

    When IF does effect the filtering:

    =SUM({0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;FALSE;1})

    Since SUM ignores logical values, we get: 3.

    It is quite easy to turn Hager's original formula

    {=SUM(1/COUNTIF(Range,Range))}

    into a SumProduct formula:

    =SUMPRODUCT(1/COUNTIF(Range,Range))

    but it isn't regarding:

    {=SUM(IF(Range<>"",1/COUNTIF(Range,Range)))}

    Whenever IF is needed when computing with array objects, a control+shift+entered formula is almost always a necessity.

    Harlan eventually arrived at:

    =SUMPRODUCT((A2:A8<>"")/COUNTIF(A2:A8,A2:A8&""))

    which is harder to understand, but becomes intelligible if one knows the following about CountIf (as touched upon at the beginning of this post)...

    ******** ******************** ************************************************************************>
    Microsoft Excel - UitlegHagerGrove.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    2
    0
    3

    1
    4
    0
    5
    1
    6
    Sheet3

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    C2:

    =COUNTIF(A2,A2)

    C5:

    =COUNTIF(A5,A5&"")

    Using our original example...

    The (A2/A8<>"") bit gives:

    {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}

    The COUNTIF(A2:A8,A2:A8&"") bit gives [ see the foregoing about the behavior of CountIf regarding "" ]...

    {3;3;3;2;2;1;1}

    The (A2:A8<>"")/COUNTIF(A2:A8,A2:A8&"") bit gives:

    {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}/{3;3;3;2;2;1;1}

    The pairwise division (with coercion of logical values into numbers) gives:

    {0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0;1}

    which gets summed, producing as result: 3.

    Concatenating Range with "" recurs also in other forms in other formulas where filtering If can be circumvented like in:

    http://www.mrexcel.com/board2/viewto...highlight=left

  7. #7
    Board Regular
    Join Date
    Sep 2003
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    So this is what a concise dictionary looks like.
    Reading is weeping
    Sorry for your missed sessions.
    Took me 3 to grasp

    Cheers.

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

    Default Re: Count distinct function?...

    I recently done my master in Business Administration and now days I want to do some online Microsoft certifications like MCSA MCSE Oracle java etc and I Found this online notes is a best helping source to get online accurate information which is more helpful for my preparation.

  9. #9
    New Member
    Join Date
    Sep 2009
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count distinct function?...

    Very interesting discussion.

    I have another issue that adds another dimension to this problem though.

    What if I had a data range that contained numerous child records.

    And lets say I needed to count the total number of distinct records for each Master record.

    Here's a sample data range:

    A B C
    1 1 Accounting
    1 1 Accounting
    1 1 Supply
    1 2 Accounting
    1 2 Marketing
    2 1 Supply
    2 1 Accounting
    2 1 Supply
    2 2 Supply

    the distinct record count is a combination of A, B, C.

    Assume on another sheet, we have A1 = 1, A2 = 2
    B1 is array formula that wil give us the answer 4, and B2 yields 3

    Any ideas?

  10. #10
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Count distinct function?...

    For simplicity, let's assume that A2:C10 contains the data, E2 contains 1, and E3 contains 2. Then, assuming that the data does not contain empty/blank cells, try...

    F2, confirmed with CONTROL+SHIFT+ENTER, and copied to down to F3:

    =SUM(IF(FREQUENCY(IF($A$2:$A$10=E2,MATCH("~"&$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10&"",0)),ROW($A$2:$A$10)-ROW($A$2)+1)>0,1))

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
  •