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

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "RJL0323" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from the email address if mailing direct)
>>
>> "RJL0323" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> 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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>>
>>> --
>>>
>>> HTH
>>>
>>> Bob Phillips
>>>
>>> (remove nothere from the email address if mailing direct)
>>>
>>> "RJL0323" <[email protected]> wrote
>>> in
>>> message news:[email protected]...
>>>>
>>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>>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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>>>
>>>> --
>>>>
>>>> HTH
>>>>
>>>> Bob Phillips
>>>>
>>>> (remove nothere from the email address if mailing direct)
>>>>
>>>> "RJL0323" <[email protected]> wrote
>>>> in
>>>> message news:[email protected]...
>>>>>
>>>>> 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" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> >> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (remove nothere from the email address if mailing direct)
> >>
> >> "RJL0323" <[email protected]> wrote
in
> >> message news:[email protected]...
> >>>
> >>> 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
> >>>
> >>
> >>
> >
> >
>

hi Peo!

for a single column of data the formula is ok!

can i use the same formula for data with more than one column with duplicates?
for example

col"a" col"b" col"c" col"d"

xxx yyy 10 zzz
xyz abc 20 rst
yzx cab 10 mno
bac def 30 xyz
xyz abc 20 rst
xyz abc 10 rst
yzx cab 10 mno

-now i want to count the number of records excluding the duplicates!
in the above example 2nd & 5th, 3rd & 6th are duplicates.

if i make a count of total records without repetition, i must a get an answer of 5 ie.(7-2)

Originally Posted by Peo Sjoblom

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from the email address if mailing direct)
>>
>> "RJL0323" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

1] If you download & install the morefunc.xll add-in:

=COUNTDIFF(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,,"")

which needs to be confirmed with control+shift+enter, not just with enter.

2] With built-in functions:

=SUMPRODUCT(--((MATCH(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,0)=ROW(INDEX(A1:A7,0,0))-ROW(A1)+1)))


via135 wrote:
> hi Peo!
>
> for a single column of data the formula is ok!
>
> can i use the same formula for data with more than one column with
> duplicates?
> for example
>
> col"a" col"b" col"c" col"d"
>
> xxx yyy 10 zzz
> xyz abc 20 rst
> yzx cab 10 mno
> bac def 30 xyz
> xyz abc 20 rst
> xyz abc 10 rst
> yzx cab 10 mno
>
> -now i want to count the number of records excluding the duplicates!
> in the above example 2nd & 5th, 3rd & 6th are duplicates.
>
> if i make a count of total records without repetition, i must a get an
> answer of 5 ie.(7-2)
>
>
>
>
>
>
> Peo Sjoblom Wrote:
>
>>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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
>>>news:%[email protected]...
>>>
>>>>=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>>>
>>>>--
>>>>
>>>>HTH
>>>>
>>>>Bob Phillips
>>>>
>>>>(remove nothere from the email address if mailing direct)
>>>>
>>>>"RJL0323" <[email protected]>
>>
>>wrote in
>>
>>>>message
>>
>>news:[email protected]...
>>
>>>>>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "RJL0323" <[email protected]> wrote
in
> > message news:[email protected]...
> >>
> >> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from the email address if mailing direct)
>> >
>> > "RJL0323" <[email protected]> wrote
> in
>> > message news:[email protected]...
>> >>
>> >> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>