Formula to count unique values

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

 

[gnaga]

How about using COUNTIF workshhet function?

 

[RobinK]

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

 

[litrelord]

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

 

[Mark W.]

For numeric values...

Book1
	A	B	C	D	E	F	G
1	3		4
2	1
3	1
4	5
5	6
6	5
7
8
Sheet1

 

[RobinK]

Wow! Thank you all so much.

 

[Aladin Akyurek]

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.