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: www.excelforum.com/member.php...oamp;userid=19456
View this thread: 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)
quot;RJL0323quot; gt; wrote in
message ...
gt;
gt; Hello All,
gt; I have a question related to counting unique values in a column of
gt; data. I will try to illustrate my question. I have a column of data
gt; with 1000 rows. In this column there are duplicated values. I would
gt; like to be able to use a function count how many unique values are in
gt; the column. Let's say there were 4 duplicates of 250 values in the
gt; column. I would like to be able to write a function to calculate the
gt; 250. I am very familiar with Excel and am able to acheive the number
gt; through subtotals and/or pivot tables. I know I can find how many
gt; instances one specific value appears in the column through sumproduct
gt; and/or countif statements, but the function to calculate the number of
gt; unique values has really got me stumped.
gt;
gt; Does anyone have any ideas?
gt;
gt; Thanks in advance!!
gt; RJ
gt;
gt;
gt; --
gt; RJL0323
gt; ------------------------------------------------------------------------
gt; RJL0323's Profile:
www.excelforum.com/member.php...oamp;userid=19456
gt; View this thread: www.excelforum.com/showthread...hreadid=513331
gt;
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
quot;RJL0323quot; gt; wrote in
message ...
gt;
gt; Hello All,
gt; I have a question related to counting unique values in a column of
gt; data. I will try to illustrate my question. I have a column of data
gt; with 1000 rows. In this column there are duplicated values. I would
gt; like to be able to use a function count how many unique values are in
gt; the column. Let's say there were 4 duplicates of 250 values in the
gt; column. I would like to be able to write a function to calculate the
gt; 250. I am very familiar with Excel and am able to acheive the number
gt; through subtotals and/or pivot tables. I know I can find how many
gt; instances one specific value appears in the column through sumproduct
gt; and/or countif statements, but the function to calculate the number of
gt; unique values has really got me stumped.
gt;
gt; Does anyone have any ideas?
gt;
gt; Thanks in advance!!
gt; RJ
gt;
gt;
gt; --
gt; RJL0323
gt; ------------------------------------------------------------------------
gt; RJL0323's Profile:
gt; www.excelforum.com/member.php...oamp;userid=19456
gt; View this thread: www.excelforum.com/showthread...hreadid=513331
gt;
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
quot;Bob Phillipsquot; gt; wrote in message
...
gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;RJL0323quot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; Hello All,
gt;gt; I have a question related to counting unique values in a column of
gt;gt; data. I will try to illustrate my question. I have a column of data
gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt;gt; like to be able to use a function count how many unique values are in
gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt;gt; column. I would like to be able to write a function to calculate the
gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt;gt; instances one specific value appears in the column through sumproduct
gt;gt; and/or countif statements, but the function to calculate the number of
gt;gt; unique values has really got me stumped.
gt;gt;
gt;gt; Does anyone have any ideas?
gt;gt;
gt;gt; Thanks in advance!!
gt;gt; RJ
gt;gt;
gt;gt;
gt;gt; --
gt;gt; RJL0323
gt;gt; ------------------------------------------------------------------------
gt;gt; RJL0323's Profile:
gt; www.excelforum.com/member.php...oamp;userid=19456
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=513331
gt;gt;
gt;
gt;
I would personally use the variant
=SUMPRODUCT(--(A1:A1000lt;gt;quot;quot;),1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;))
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
4it 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
quot;Bill Kuundersquot; gt; wrote in message
...
gt; Bob, I check this news group frequently as a means to learn stuff.
gt;
gt; Could you please explain why and how your formula works?
gt;
gt; Thank You
gt; --
gt; Greetings from New Zealand
gt; Bill K
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt;gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt;gt;
gt;gt; --
gt;gt;
gt;gt; HTH
gt;gt;
gt;gt; Bob Phillips
gt;gt;
gt;gt; (remove nothere from the email address if mailing direct)
gt;gt;
gt;gt; quot;RJL0323quot; gt; wrote in
gt;gt; message ...
gt;gt;gt;
gt;gt;gt; Hello All,
gt;gt;gt; I have a question related to counting unique values in a column of
gt;gt;gt; data. I will try to illustrate my question. I have a column of data
gt;gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt;gt;gt; like to be able to use a function count how many unique values are in
gt;gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt;gt;gt; column. I would like to be able to write a function to calculate the
gt;gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt;gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt;gt;gt; instances one specific value appears in the column through sumproduct
gt;gt;gt; and/or countif statements, but the function to calculate the number of
gt;gt;gt; unique values has really got me stumped.
gt;gt;gt;
gt;gt;gt; Does anyone have any ideas?
gt;gt;gt;
gt;gt;gt; Thanks in advance!!
gt;gt;gt; RJ
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; RJL0323
gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt; RJL0323's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=19456
gt;gt;gt; View this thread:
gt;gt;gt; www.excelforum.com/showthread...hreadid=513331
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;Thanks Peo,
When I only enter = 1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;) the answer is 0.5
I suppose it is showing me the most frequent occuring in the array??
Thanks again, excellent formula
Bill Kuunders
quot;Peo Sjoblomquot; gt; wrote in message
...
gt;I would personally use the variant
gt;
gt; =SUMPRODUCT(--(A1:A1000lt;gt;quot;quot;),1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;))
gt;
gt; otherwise you'll get DIV/0 errors if there are blank cells, it works as
gt; follows
gt;
gt; the 1/countif part returns an array of numbers, if there is one value
gt; unique it will return 1,
gt; if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
gt; 0.5), if 3 it will return 0.333333, 4 0.25 and so on
gt;
gt; assume we have this in A1:A10
gt;
gt; 1
gt; 2
gt; 3
gt; 4
gt; 65
gt; 6
gt; 1
gt; 2
gt; 3
gt; 4
gt;
gt;
gt; it would be 6 unique values, the 1/countif returns
gt;
gt; {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}
gt;
gt; sumproduct will sum them to return 6, if we change the last number 4 to 1
gt; so there would be 3 1
gt;
gt; {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333}
gt;
gt; still returns the total of 6
gt;
gt; I believe former MVP Dave Hager was the originator of it although it has
gt; been converted from
gt;
gt; =SUM(1/COUNTIF))
gt;
gt; to sumproduct thus it can be entered normally
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Bill Kuundersquot; gt; wrote in message
gt; ...
gt;gt; Bob, I check this news group frequently as a means to learn stuff.
gt;gt;
gt;gt; Could you please explain why and how your formula works?
gt;gt;
gt;gt; Thank You
gt;gt; --
gt;gt; Greetings from New Zealand
gt;gt; Bill K
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt;
gt;gt;gt; HTH
gt;gt;gt;
gt;gt;gt; Bob Phillips
gt;gt;gt;
gt;gt;gt; (remove nothere from the email address if mailing direct)
gt;gt;gt;
gt;gt;gt; quot;RJL0323quot; gt; wrote
gt;gt;gt; in
gt;gt;gt; message ...
gt;gt;gt;gt;
gt;gt;gt;gt; Hello All,
gt;gt;gt;gt; I have a question related to counting unique values in a column of
gt;gt;gt;gt; data. I will try to illustrate my question. I have a column of data
gt;gt;gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt;gt;gt;gt; like to be able to use a function count how many unique values are in
gt;gt;gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt;gt;gt;gt; column. I would like to be able to write a function to calculate the
gt;gt;gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt;gt;gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt;gt;gt;gt; instances one specific value appears in the column through sumproduct
gt;gt;gt;gt; and/or countif statements, but the function to calculate the number of
gt;gt;gt;gt; unique values has really got me stumped.
gt;gt;gt;gt;
gt;gt;gt;gt; Does anyone have any ideas?
gt;gt;gt;gt;
gt;gt;gt;gt; Thanks in advance!!
gt;gt;gt;gt; RJ
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt; RJL0323
gt;gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt;gt; RJL0323's Profile:
gt;gt;gt; www.excelforum.com/member.php...oamp;userid=19456
gt;gt;gt;gt; View this thread:
gt;gt;gt;gt; www.excelforum.com/showthread...hreadid=513331
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
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 amp; 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
quot;Bill Kuundersquot; gt; wrote in message
...
gt; Thanks Peo,
gt;
gt; When I only enter = 1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;) the answer is 0.5
gt; I suppose it is showing me the most frequent occuring in the array??
gt;
gt; Thanks again, excellent formula
gt; Bill Kuunders
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt;gt;I would personally use the variant
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A1000lt;gt;quot;quot;),1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;))
gt;gt;
gt;gt; otherwise you'll get DIV/0 errors if there are blank cells, it works as
gt;gt; follows
gt;gt;
gt;gt; the 1/countif part returns an array of numbers, if there is one value
gt;gt; unique it will return 1,
gt;gt; if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
gt;gt; 0.5), if 3 it will return 0.333333, 4 0.25 and so on
gt;gt;
gt;gt; assume we have this in A1:A10
gt;gt;
gt;gt; 1
gt;gt; 2
gt;gt; 3
gt;gt; 4
gt;gt; 65
gt;gt; 6
gt;gt; 1
gt;gt; 2
gt;gt; 3
gt;gt; 4
gt;gt;
gt;gt;
gt;gt; it would be 6 unique values, the 1/countif returns
gt;gt;
gt;gt; {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}
gt;gt;
gt;gt; sumproduct will sum them to return 6, if we change the last number 4 to 1
gt;gt; so there would be 3 1
gt;gt;
gt;gt; {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333}
gt;gt;
gt;gt; still returns the total of 6
gt;gt;
gt;gt; I believe former MVP Dave Hager was the originator of it although it has
gt;gt; been converted from
gt;gt;
gt;gt; =SUM(1/COUNTIF))
gt;gt;
gt;gt; to sumproduct thus it can be entered normally
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; Northwest Excel Solutions
gt;gt;
gt;gt; Portland, Oregon
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Bill Kuundersquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Bob, I check this news group frequently as a means to learn stuff.
gt;gt;gt;
gt;gt;gt; Could you please explain why and how your formula works?
gt;gt;gt;
gt;gt;gt; Thank You
gt;gt;gt; --
gt;gt;gt; Greetings from New Zealand
gt;gt;gt; Bill K
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt;
gt;gt;gt;gt; HTH
gt;gt;gt;gt;
gt;gt;gt;gt; Bob Phillips
gt;gt;gt;gt;
gt;gt;gt;gt; (remove nothere from the email address if mailing direct)
gt;gt;gt;gt;
gt;gt;gt;gt; quot;RJL0323quot; gt; wrote
gt;gt;gt;gt; in
gt;gt;gt;gt; message ...
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Hello All,
gt;gt;gt;gt;gt; I have a question related to counting unique values in a column of
gt;gt;gt;gt;gt; data. I will try to illustrate my question. I have a column of data
gt;gt;gt;gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt;gt;gt;gt;gt; like to be able to use a function count how many unique values are in
gt;gt;gt;gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt;gt;gt;gt;gt; column. I would like to be able to write a function to calculate the
gt;gt;gt;gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt;gt;gt;gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt;gt;gt;gt;gt; instances one specific value appears in the column through sumproduct
gt;gt;gt;gt;gt; and/or countif statements, but the function to calculate the number of
gt;gt;gt;gt;gt; unique values has really got me stumped.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Does anyone have any ideas?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Thanks in advance!!
gt;gt;gt;gt;gt; RJ
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt; RJL0323
gt;gt;gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt;gt;gt; RJL0323's Profile:
gt;gt;gt;gt; www.excelforum.com/member.php...oamp;userid=19456
gt;gt;gt;gt;gt; View this thread:
gt;gt;gt;gt;gt; www.excelforum.com/showthread...hreadid=513331
gt;gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;
gt;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)
quot;Peo Sjoblomquot; gt; wrote in message
...
gt; I would personally use the variant
gt;
gt; =SUMPRODUCT(--(A1:A1000lt;gt;quot;quot;),1/COUNTIF(A1:A1000,A1:A1000amp;quot;quot;))
gt;
gt; otherwise you'll get DIV/0 errors if there are blank cells, it works as
gt; follows
gt;
gt; the 1/countif part returns an array of numbers, if there is one value
unique
gt; it will return 1,
gt; if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
gt; 0.5), if 3 it will return 0.333333, 4 0.25 and so on
gt;
gt; assume we have this in A1:A10
gt;
gt; 1
gt; 2
gt; 3
gt; 4
gt; 65
gt; 6
gt; 1
gt; 2
gt; 3
gt; 4
gt;
gt;
gt; it would be 6 unique values, the 1/countif returns
gt;
gt; {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}
gt;
gt; sumproduct will sum them to return 6, if we change the last number 4 to 1
so
gt; there would be 3 1
gt;
gt;
{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333
}
gt;
gt; still returns the total of 6
gt;
gt; I believe former MVP Dave Hager was the originator of it although it has
gt; been converted from
gt;
gt; =SUM(1/COUNTIF))
gt;
gt; to sumproduct thus it can be entered normally
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Bill Kuundersquot; gt; wrote in message
gt; ...
gt; gt; Bob, I check this news group frequently as a means to learn stuff.
gt; gt;
gt; gt; Could you please explain why and how your formula works?
gt; gt;
gt; gt; Thank You
gt; gt; --
gt; gt; Greetings from New Zealand
gt; gt; Bill K
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; HTH
gt; gt;gt;
gt; gt;gt; Bob Phillips
gt; gt;gt;
gt; gt;gt; (remove nothere from the email address if mailing direct)
gt; gt;gt;
gt; gt;gt; quot;RJL0323quot; gt; wrote
in
gt; gt;gt; message ...
gt; gt;gt;gt;
gt; gt;gt;gt; Hello All,
gt; gt;gt;gt; I have a question related to counting unique values in a column of
gt; gt;gt;gt; data. I will try to illustrate my question. I have a column of data
gt; gt;gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt; gt;gt;gt; like to be able to use a function count how many unique values are in
gt; gt;gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt; gt;gt;gt; column. I would like to be able to write a function to calculate the
gt; gt;gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt; gt;gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt; gt;gt;gt; instances one specific value appears in the column through sumproduct
gt; gt;gt;gt; and/or countif statements, but the function to calculate the number of
gt; gt;gt;gt; unique values has really got me stumped.
gt; gt;gt;gt;
gt; gt;gt;gt; Does anyone have any ideas?
gt; gt;gt;gt;
gt; gt;gt;gt; Thanks in advance!!
gt; gt;gt;gt; RJ
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt; --
gt; gt;gt;gt; RJL0323
gt;
gt;gt;gt; ------------------------------------------------------------------------
gt; gt;gt;gt; RJL0323's Profile:
gt; gt;gt; www.excelforum.com/member.php...oamp;userid=19456
gt; gt;gt;gt; View this thread:
gt; gt;gt;gt; www.excelforum.com/showthread...hreadid=513331
gt; gt;gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
Bill,
I had a go at explaining it a while back in 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)
quot;Bill Kuundersquot; gt; wrote in message
...
gt; Bob, I check this news group frequently as a means to learn stuff.
gt;
gt; Could you please explain why and how your formula works?
gt;
gt; Thank You
gt; --
gt; Greetings from New Zealand
gt; Bill K
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;RJL0323quot; gt; wrote
in
gt; gt; message ...
gt; gt;gt;
gt; gt;gt; Hello All,
gt; gt;gt; I have a question related to counting unique values in a column of
gt; gt;gt; data. I will try to illustrate my question. I have a column of data
gt; gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt; gt;gt; like to be able to use a function count how many unique values are in
gt; gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt; gt;gt; column. I would like to be able to write a function to calculate the
gt; gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt; gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt; gt;gt; instances one specific value appears in the column through sumproduct
gt; gt;gt; and/or countif statements, but the function to calculate the number of
gt; gt;gt; unique values has really got me stumped.
gt; gt;gt;
gt; gt;gt; Does anyone have any ideas?
gt; gt;gt;
gt; gt;gt; Thanks in advance!!
gt; gt;gt; RJ
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; RJL0323
gt;
gt;gt; ------------------------------------------------------------------------
gt; gt;gt; RJL0323's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=19456
gt; gt;gt; View this thread:
gt; gt;gt; www.excelforum.com/showthread...hreadid=513331
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Thank You Both
Peo and Bob
I appreciate your time and effort very much.
Bill Kuunders
quot;Bob Phillipsquot; gt; wrote in message
...
gt; Bill,
gt;
gt; I had a go at explaining it a while back in tinyurl.com/dhbxe. This
gt; explanation is based upon the version that caters for blanks, but the
gt; fundamental principle is the same.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Bill Kuundersquot; gt; wrote in message
gt; ...
gt;gt; Bob, I check this news group frequently as a means to learn stuff.
gt;gt;
gt;gt; Could you please explain why and how your formula works?
gt;gt;
gt;gt; Thank You
gt;gt; --
gt;gt; Greetings from New Zealand
gt;gt; Bill K
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; HTH
gt;gt; gt;
gt;gt; gt; Bob Phillips
gt;gt; gt;
gt;gt; gt; (remove nothere from the email address if mailing direct)
gt;gt; gt;
gt;gt; gt; quot;RJL0323quot; gt; wrote
gt; in
gt;gt; gt; message ...
gt;gt; gt;gt;
gt;gt; gt;gt; Hello All,
gt;gt; gt;gt; I have a question related to counting unique values in a column of
gt;gt; gt;gt; data. I will try to illustrate my question. I have a column of data
gt;gt; gt;gt; with 1000 rows. In this column there are duplicated values. I would
gt;gt; gt;gt; like to be able to use a function count how many unique values are in
gt;gt; gt;gt; the column. Let's say there were 4 duplicates of 250 values in the
gt;gt; gt;gt; column. I would like to be able to write a function to calculate the
gt;gt; gt;gt; 250. I am very familiar with Excel and am able to acheive the number
gt;gt; gt;gt; through subtotals and/or pivot tables. I know I can find how many
gt;gt; gt;gt; instances one specific value appears in the column through sumproduct
gt;gt; gt;gt; and/or countif statements, but the function to calculate the number of
gt;gt; gt;gt; unique values has really got me stumped.
gt;gt; gt;gt;
gt;gt; gt;gt; Does anyone have any ideas?
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks in advance!!
gt;gt; gt;gt; RJ
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; RJL0323
gt;gt;
gt;gt;gt; ------------------------------------------------------------------------
gt;gt; gt;gt; RJL0323's Profile:
gt;gt; gt; www.excelforum.com/member.php...oamp;userid=19456
gt;gt; gt;gt; View this thread:
gt;gt; gt;gt; www.excelforum.com/showthread...hreadid=513331
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
留言列表
