Count Unique values

[goofy78270]

I can get a count for the number of cells within a column or row, but how can i find out how many unique values there are?

ie
4
5
6
7
6
5
8
9

While a count will give me 8 values, I only want to return 6 for the unique references

 

[galileogali]

tRY WITH:

=SUM(N(FREQUENCY(A7:A19;A7:A19)>0))


cONFIRM WITH ctrl+shift+enter


GALILEOGALI

 

[Aladin Akyurek]

Control+shift+enter...

=SUM(IF(FREQUENCY(A2:A7,A2:A7),1))

 

[tusharm]

I imagine the others have some reason for suggesting the FREQUENCY function but I would use the array formula
=SUM(1/COUNTIF(K3:K10,K3:K10))
where K3K10 contained the test data set.

FREQUENCY doesn't work if the data set contains non-numbers.

To correctly enter an array formula complete data entry with the combination CTRL+SHIFT+ENTER. If done correctly, *Excel* will show the formula enclosed in curly braces { and }

I can get a count for the number of cells within a column or row, but how can i find out how many unique values there are?

ie
4
5
6
7
6
5
8
9

While a count will give me 8 values, I only want to return 6 for the unique references

 

[galileogali]

and then more....

Libro1
	A	B	C	D	E
1	4	q
2	5			8
3	6	9
4	7
5	q	6		but..
6		5
7	e			#DIV/0!
8	r	e
9
Hoja1

GALILEOGALI

 

[goofy78270]

Along the same line, how would I combine the unique values into 1 string such as "4, 5, 6, 7, q, e, r, 9" The order does not matter to much, but I would like it to order in the way the values are found in the column or range.

 

[Aladin Akyurek]

Along the same line, how would I combine the unique values into 1 string such as "4, 5, 6, 7, q, e, r, 9" The order does not matter to much, but I would like it to order in the way the values are found in the column or range.

If you download and install the free morefunc.xll add-in...

=SUBSTITUTE(TRIM(MCONCAT(UNIQUEVALUES(A2:A11)," "))," ",", ")

BTW, just to set the record straight regarding 'unique item count'...

1]

=SUM(IF(FREQUENCY(A2:A7,A2:A7),1))

will calculate a unique count of numbers. It will disregard the non-numbers. (Your original question as stated involved 'numbers')

2]

=SUM(1/COUNTIF(A2:A7,A2:A7))

Requires control+shift+enter.

3]

=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")

Here are some links on [2] and [3]...

http://www.mrexcel.com/board2/viewtopic.php?t=37550&highlight=token
http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager
http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grove

4] With a function from the free morefunc.xll add-in...

=COUNTDIFF(A2:A7,,"")

which is case-sensitive.

5] Latest on the subject...

=SUM(IF(FREQUENCY(IF(A2:A7<>"",MATCH("~"&A2:A7,A2:A7&"",0)),ROW(A2:A7)-ROW(A2)+1),1))

Requires control+shift+enter.

[5] is designed to circumvent some of the issues that turn up with items containing symbols with special meaning (like < ), although it doesn't resolve all of them.

I might add that [4] is the best and the fastest.

BTW, the above does not list some formulas that invoke FREQUENCY and MATCH...

 

[galileogali]

If I were Laurent L., I would now say: "my morefunc addin is morefunc 'AL ADD IN' ".

GALILEOGALI

 

[b2b213]

I imagine the others have some reason for suggesting the FREQUENCY function but I would use the array formula
=SUM(1/COUNTIF(K3:K10,K3:K10))
where K3K10 contained the test data set.

FREQUENCY doesn't work if the data set contains non-numbers.

To correctly enter an array formula complete data entry with the combination CTRL+SHIFT+ENTER. If done correctly, *Excel* will show the formula enclosed in curly braces { and }

Can this be used on a column with an unspecifed range or does the range need to be set to the exact size of the of the data set?