# 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. ## 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. ## Re: Formula to count unique values

How about using COUNTIF workshhet function?

3. ## 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. ## 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. ## 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
 C1 =

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. ## Re: Formula to count unique values

Wow! Thank you all so much.

7. ## Re: Formula to count unique values

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.

#### Posting Permissions

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