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
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 more:
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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks