Thanks:  0
Likes:  0

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

1. I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean

2. I assume by unique numbers, you merely want to count how many different numbers appear in the list?

If so, a Pivot Table would do it quite easily.

3. you could try sorting your list and using the Data-> Subtotal function.

This groups up your data and subtotals the group.

4. On 2002-03-05 04:53, Anonymous wrote:
I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean
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)))

5. In addition to the responses already supplied, here's a UDF :-

Function Uniques(Rng As Range) As Long
Dim cell As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each cell In Rng
Next cell
On Error GoTo 0
Uniques = UniqueValues.Count
End Function

6. ## Re: How do you Count the number of unique values in a list ?

What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.

P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all!

7. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by Tazguy37
What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.

P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all!
For a list of unique items, see,

http://216.92.17.166/board2/viewtopic.php?t=8659

8. ## Re: How do you Count the number of unique values in a list ?

Hi Taz:

Welcome to MrExcel Board!

Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

I am sorry ... I did not understand the part of your statement ...
There could be small differences between the items, which you would want accounted for.

9. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by Yogi Anand
Hi Taz:

Welcome to MrExcel Board!

Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

I am sorry ... I did not understand the part of your statement ...
There could be small differences between the items, which you would want accounted for.
Thanks for the reply, Yogi! Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told ).

In the 2nd part of my post, I just meant that you'd want to count all the unique items, not just ones that are "close." You can ignore that part...no worries.

Thanks for all the help!

10. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by Tazguy37
...
Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told ).
My contrib in

http://216.92.17.166/board2/viewtopi...light=distinct

describes a fast formula system to extract unique/distinct items from a range.

Method 1 (Brian refers to) in:

http://216.92.17.166/board2/viewtopi...r=asc&start=20

sorts and extracts such a list.

## User Tag List

#### Posting Permissions

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