Counting Values

[Alan]

Hello All

Yet another problem ... have spreadsheed with multiple rows of data, however
I am looking to find a way of counting unique entries ...

ie data in colums such as ... 123 234 345 456 123 345 789

I am looking for a count function to tell me that there are 5 different
indivdual values within this set ... not lookking for number of entries just
identifying the different unique individual entries

Many Thanks

Alan

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20= "")))

 

[Alan]

Thanks Frank ... however that didnt work for me here ... some of my sheets
also contain more than 20 rows

 

[Frank Kabel]

Hi
then just change the range reference. e.g. change
A1:A20 (all occurences) to
A1:A100 (or whatever is required)

 

[Gord Dibben]

Alan

Assuming the data are in A1 to G1 and always numbers.

=SUM(N(FREQUENCY(A1:G1,A1:G1)>0)) returns 5

If a mix of numbers and includes blank cells see Chip Pearson's site for
counting uniques in a range with more complex parameters.

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates


Gord Dibben Excel MVP

 

[Harlan Grove]

Assuming the data are in A1 to G1 and always numbers.

=SUM(N(FREQUENCY(A1:G1,A1:G1)>0)) returns 5

....

Fewer nested function calls is usually better.

=COUNT(1/FREQUENCY(A1:G1,A1:G1))

 

[annie]

Thanks Harlan!
I also get help from your answer!
thank you very much!