there are 3 values in a column. they are 2, 2, 3. what formula or function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

=SUMPRODUCT(1/COUNTIF(Range,Range))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or
function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

Hi

if your data is only numeric and doesn't contain blanks you can use the
following formula from Chip Pearson's web site
=SUM(N(FREQUENCY(A5:A7,A5:A7)0))

where A5:A7 is the range of your values.

If you will be dealing with text or your data could have blanks in it -
check out Chip's page on duplicates at:
http://www.cpearson.com/excel/duplicat.htm
- about half way down you'll find an article on counting unique entries in a
range.

Cheers
JulieD


"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or
function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

The formula below assumes your data is in the range A1:A3. Enter this
formula as an array (select the range A1:A3, put the formula in the formula
bar, and press CTRL - SHIFT - ENTER at the same time):

=SUM(IF(FREQUENCY(A1:A3,A1:A3)0,1))

This Microsoft Knowledgebse article explains things a little mo

http://support.microsoft.com/kb/q268001/

----
Regards,
John Mansfield
http://www.pdbook.com
"LatinViolin" wrote:

there are 3 values in a column. they are 2, 2, 3. what formula or function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

If the column might contain blank cells, try this:

=SUMPRODUCT((A1:A30<"")/COUNTIF(A1:A30,A1:A30&""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

thanks for your answer. great!

"Ken Wright" wrote:

=SUMPRODUCT(1/COUNTIF(Range,Range))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or
function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

thanks for your answer

"JulieD" wrote:

Hi

if your data is only numeric and doesn't contain blanks you can use the
following formula from Chip Pearson's web site
=SUM(N(FREQUENCY(A5:A7,A5:A7)0))

where A5:A7 is the range of your values.

If you will be dealing with text or your data could have blanks in it -
check out Chip's page on duplicates at:
http://www.cpearson.com/excel/duplicat.htm
- about half way down you'll find an article on counting unique entries in a
range.

Cheers
JulieD


"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or
function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

it works perfect. that's all i needed.

"RagDyeR" wrote:

If the column might contain blank cells, try this:

=SUMPRODUCT((A1:A30<"")/COUNTIF(A1:A30,A1:A30&""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man

"RagDyeR" wrote:

If the column might contain blank cells, try this:

=SUMPRODUCT((A1:A30<"")/COUNTIF(A1:A30,A1:A30&""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"LatinViolin" wrote in message
...
there are 3 values in a column. they are 2, 2, 3. what formula or function
do i use to determine how many different values exist in this set. the
answer is 2. there are two different values, 2 & 3. please help
--
cell man