Counting Unique Values

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456
View this thread: http://www.excelforum.com/showthread...hreadid=513331

=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread: http://www.excelforum.com/showthread...hreadid=513331

One way.
enter =count(E2:E1002) in cell E1
select the range in question
go to ....data....filter....advanced filter....copy to another location
select E2
tick unique records only............. no need for a criteria range

Greetings from New Zealand
Bill K
"RJL0323" wrote in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread: http://www.excelforum.com/showthread...hreadid=513331

Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

I would personally use the variant

=SUMPRODUCT(--(A1:A1000""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works as
follows

the 1/countif part returns an array of numbers, if there is one value unique
it will return 1,
if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4 to 1 so
there would be 3 1

{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

Thanks Peo,

When I only enter = 1/COUNTIF(A1:A1000,A1:A1000&"") the answer is 0.5
I suppose it is showing me the most frequent occuring in the array??

Thanks again, excellent formula
Bill Kuunders

"Peo Sjoblom" wrote in message
...
I would personally use the variant

=SUMPRODUCT(--(A1:A1000""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works as
follows

the 1/countif part returns an array of numbers, if there is one value
unique it will return 1,
if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4 to 1
so there would be 3 1

{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

Actually, the 0.5 is the first value in the array, meaning that whatever is
in A1 occurs twice in the range

select B1:B10, with B1 as the active cell click in the formula bar and put
that formula, now enter it with ctrl + shift & enter, now if you sum B1:B10
you'll get the total of uniques, that is how array formulas can work

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Thanks Peo,

When I only enter = 1/COUNTIF(A1:A1000,A1:A1000&"") the answer is 0.5
I suppose it is showing me the most frequent occuring in the array??

Thanks again, excellent formula
Bill Kuunders

"Peo Sjoblom" wrote in message
...
I would personally use the variant

=SUMPRODUCT(--(A1:A1000""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works as
follows

the 1/countif part returns an array of numbers, if there is one value
unique it will return 1,
if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4 to 1
so there would be 3 1

{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

From the OPs post there was a full set of data, so in his case there was no
need to handle blanks.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Peo Sjoblom" wrote in message
...
I would personally use the variant

=SUMPRODUCT(--(A1:A1000""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works as
follows

the 1/countif part returns an array of numbers, if there is one value
unique
it will return 1,
if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4 to 1
so
there would be 3 1


{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333
}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323

------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

Bill,

I had a go at explaining it a while back in http://tinyurl.com/dhbxe. This
explanation is based upon the version that caters for blanks, but the
fundamental principle is the same.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323

------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331

Thank You Both

Peo and Bob

I appreciate your time and effort very much.

Bill Kuunders

"Bob Phillips" wrote in message
...
Bill,

I had a go at explaining it a while back in http://tinyurl.com/dhbxe. This
explanation is based upon the version that caters for blanks, but the
fundamental principle is the same.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323

------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331