Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel
Hi everyone, I need to count the number of different values in rows of data. <br><br>For example, in 100 rows of data about Gender, there are only two values, F and M, so I would like the function to return 2, even if I select 100 rows all with data. <br><br>Another example: <br>
Given the following list: <br><br>apple <br>
orange <br>
berry <br>
berry <br>
apple <br>
banana <br>
apple <br><br>The function would return 4 since there are four different values, apple, orange, berry banana <br><br>Does this function exist in Excel? <br><br>Thanks, this will be very helpful to a research project
|
|
0
|
|
|
|
Reply
|
 uOttawaGeek
|
2/2/2010 10:40:28 PM |
|
Hi Ottowa, <br><br>I'm not aware of a function to do this, but I've gotten the same result this way: <br><br>-> Copy data into empty sheet, so original doesn't get damaged, <br><br>-> Sort the data column, it doesn't matter how, <br><br>-> In the adjacent column put an IF test in each cell, that returns "1" if the cell on the left and the one above are different, "0" otherwise, <br><br>-> Fill down so all the data items in the data column are being compared to their neighbors above, <br><br>-> Sum up the number of "1" values in the second column and add one for good measure. <br><br>I tried this just now, and cell B2 in my sheet contained this formula: <br><br> =IF(A1<>A2,1,0) <br><br>Don't forget to sort the data, and to account for spaces, case sensitivity, etc. Good idea to try this technique out first on dummy data where you know the answer ahead of time.
|
|
|
0
|
|
|
|
Reply
|
williamm
|
2/3/2010 4:31:10 PM
|
|
> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--B_3348045508_72502484
Content-type: text/plain;
charset="US-ASCII"
Content-transfer-encoding: 7bit
On 2/2/10 5:40 PM, in article 59bb221a.-1@webcrossing.JaKIaxP2ac0,
"uOttawaGeek@officeformac.com" <uOttawaGeek@officeformac.com> wrote:
> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> Hi everyone, I need to count the number of different values in rows of data.
>
> For example, in 100 rows of data about Gender, there are only two values, F
> and M, so I would like the function to return 2, even if I select 100 rows all
> with data.
>
> Another example:
> Given the following list:
>
> apple
> orange
> berry
> berry
> apple
> banana
> apple
>
> The function would return 4 since there are four different values, apple,
> orange, berry banana
>
> Does this function exist in Excel?
>
> Thanks, this will be very helpful to a research project
Another way is to do an advanced filter and specify unique only. Then count
the result.
--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom
--B_3348045508_72502484
Content-type: text/html;
charset="US-ASCII"
Content-transfer-encoding: quoted-printable
<HTML>
<HEAD>
<TITLE>Re: Counting the number of unique different values in a set</TITLE>
</HEAD>
<BODY>
<FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-size:10pt'>On 2/2/10 5:40 PM, =
in article <a href=3D"59bb221a.-1@webcrossing.JaKIaxP2ac0">59bb221a.-1@webcros=
sing.JaKIaxP2ac0</a>, "<a href=3D"uOttawaGeek@officeformac.com">uOttawaGe=
ek@officeformac.com</a>" <<a href=3D"uOttawaGeek@officeformac.com">uOt=
tawaGeek@officeformac.com</a>> wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-size=
:10pt'>Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processo=
r: Intel Hi everyone, I need to count the number of different values in rows=
of data. <BR>
<BR>
For example, in 100 rows of data about Gender, there are only two values, F=
and M, so I would like the function to return 2, even if I select 100 rows =
all with data. <BR>
<BR>
Another example: <BR>
Given the following list: <BR>
<BR>
apple <BR>
orange <BR>
berry <BR>
berry <BR>
apple <BR>
banana <BR>
apple <BR>
<BR>
The function would return 4 since there are four different values, apple, o=
range, berry banana <BR>
<BR>
Does this function exist in Excel? <BR>
<BR>
Thanks, this will be very helpful to a research project<BR>
</SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-siz=
e:10pt'>Another way is to do an advanced filter and specify unique only. The=
n count the result.<BR>
</SPAN></FONT><FONT SIZE=3D"2"><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN S=
TYLE=3D'font-size:9pt'><BR>
-- <BR>
Bob Greenblatt [MVP], Macintosh<BR>
bobgreenblattATmsnDOTcom</SPAN></FONT></FONT><FONT FACE=3D"Lucida Grande"><SP=
AN STYLE=3D'font-size:10pt'><BR>
</SPAN></FONT>
</BODY>
</HTML>
--B_3348045508_72502484--
|
|
|
0
|
|
|
|
Reply
|
Bob
|
2/3/2010 5:38:27 PM
|
|
|
2 Replies
415 Views
(page loaded in 0.047 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
1146 articles. 
14 followers. 