count
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 |
All times are GMT +1. The time now is 04:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com