Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count unique text values in a range (Excel 2000)

    How do I count the number of UNIQUE text values in a range? For example, cells A1 through A5 have the following values: apples, apples, oranges, peaches, bananas. The count of unique text values is 4. What kind of formula can I use to get this calculation?

    Any help is much appreciated. Thanks.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count unique text values in a range (Excel 2000)

    If there ane no blank cells in A1 thru A5, then the formula is not too bad:
    <pre>{=SUM(1/COUNTIF(A1:A5, A1:A5))}</pre>

    where the braces are placed there by Excel as you enter this as an array formula (press Ctrl-Shift-Enter instead of Enter)

    If there can be blanks, then it is even stranger
    <pre>{=SUM(IF(COUNTIF(A1:A5,A1:A5)=0, "", 1/COUNTIF(A1:A5,A1:A5)))}</pre>

    again entered as an array formula.

    No, I'm not the genius, this is all on John Walkenbach's site j-walk.com --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count unique text values in a range (Excel 2000)

    You're a doll, even if you did get it from j-walk.com.
    Thanks,
    Linda

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count unique text values in a range (Excel 2000)

    In case of (formula-generated) blanks I'd suggest using:

    =SUM(IF(LEN(A1:A5),1/COUNTIF(A1:A5,A1:A5)))

    array-entered of course.

    The particular use of COUNTIF is an invention of David Hager. Enforcing it with the LEN test originates from my efforts in fighting the formula-generated blanks.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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