Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm looking for something like COUNTA() that will count the number of unique values in a column. It would be OK if the column had to be sorted first, but I'm hoping for something better.

    When you turn on AutoFilter in a worksheet and click the arrow on the dropdown box at the top of any column, you see the unique values right there and instantaneously. That leads me to think that the function I want might already exist, or at least be easy to create.

    What say the Gurus?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [
    What say the Gurus?
    [/quote]

    Found the below on the MS Web site.

    They ar both array formuals.

    If your data is in B2 to B10 and contains no blank cells

    The formula counts the number of unique text and number values in cells B2:B10 (which must not contain blank cells)


    =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2: B10,B2:B10,0))>0,1))

    It is an array formula so enter with CTR +Shift + Enter

    OR

    Count the number of unique text and number values in cells B2:B10 , but do not count blank cells or text values

    =SUM(IF(FREQUENCY(IF(LEN(B2:B10)>0,MATCH(B2:B10,B2 :B10,0),""), IF(LEN(B2:B10)>0,MATCH(B2:B10,B2:B10,0),""))>0,1))

    It is an array formula so enter with CTR +Shift + Enter

    Regards,

    Tom Duthie

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Lou

    A simpler function which will count both numbers and text is:

    =SUM(1/COUNTIF(A1:A100,A1:A100))

    If you confirm it by pressing Ctrl + Shift + Enter

    Ensure that the range, A have assumed A1:A100 is the exact range otherwise you will get a #DIV/0! Error

    HTH
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Lou Sander' post='774173' date='08-May-2009 03:13']I'm looking for something like COUNTA() that will count the number of unique values in a column. It would be OK if the column had to be sorted first, but I'm hoping for something better.

    When you turn on AutoFilter in a worksheet and click the arrow on the dropdown box at the top of any column, you see the unique values right there and instantaneously. That leads me to think that the function I want might already exist, or at least be easy to create.

    What say the Gurus?[/quote]


    One other way with non array formula ( just press Enter )

    assume your range start from col A row 2

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

    HTH
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Thanks, guys! I'm very busy at the moment and can't try your suggestions, but I WILL be back soon.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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