Counting Values
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany


Alan wrote:
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

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



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


Alan wrote:
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

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

--
Regards
Frank Kabel
Frankfurt, Germany


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



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


Alan wrote:
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

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/duplic...gingDuplicates


Gord Dibben Excel MVP

On Thu, 25 Nov 2004 10:17:03 -0800, "Alan"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote...
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))

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

"Harlan Grove" wrote:

"Gord Dibben" <gorddibbATshawDOTca wrote...
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))