Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By Aladin Akyurek

Formula to count unique values

This is a discussion on Formula to count unique values within the Excel Questions forums, part of the Question Forums category; Is there a formula I can put in a cell to count the number of uniqe values/records in a column? ...

  1. #1
    New Member
    Join Date
    May 2003
    Location
    Hawaii
    Posts
    16

    Default Formula to count unique values

    Is there a formula I can put in a cell to count the number of uniqe values/records in a column?

    I know I can go thru the Advance Filter process to derive a unique list and then count the list but I was wondering if there is a formula I could use instead?

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Posts
    604

    Default Re: Formula to count unique values

    How about using COUNTIF workshhet function?
    Regards
    GNaga

  3. #3
    New Member
    Join Date
    May 2003
    Location
    Hawaii
    Posts
    16

    Default Re: Formula to count unique values

    You mean like countif A2<>A1? But what would the formula look likeI want to count a column range?

  4. #4
    Board Regular
    Join Date
    Dec 2002
    Posts
    519

    Default Re: Formula to count unique values

    The following formula was taken from Chip Pearsons website at this address: -

    http://www.cpearson.com/excel/duplicat.htm

    =SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
    IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

    The majority of the site is worth a look, it's saved me loads of time in the past and will probably continue to do so.

    This assumes that you have given the range you want to check the name "Range1".

    It also has to be entered as an array formula so after entering it press Ctrl & Shift & Enter together instead of just pressing enter.

    Regards

    Nick

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Formula to count unique values

    For numeric values...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : 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
    E
    F
    G
    1
    3*4****
    2
    1******
    3
    1******
    4
    5******
    5
    6******
    6
    5******
    7
    *******
    8
    *******
    Sheet1*

    [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.

  6. #6
    New Member
    Join Date
    May 2003
    Location
    Hawaii
    Posts
    16

    Default Re: Formula to count unique values

    Wow! Thank you all so much.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,203

    Default Re: Formula to count unique values

    Quote Originally Posted by RobinK
    Is there a formula I can put in a cell to count the number of uniqe values/records in a column?

    I know I can go thru the Advance Filter process to derive a unique list and then count the list but I was wondering if there is a formula I could use instead?
    =SUMPRODUCT((Range<>"")/(COUNTIF(Range,Range&"")))

    Or, the faster...

    =COUNTDIFF(Range)

    if you don't have formula-blanks in Range. This function requires the morefunc add-in.
    nuked likes this.
    Assuming too much and qualifying too much are two faces of the same problem.

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