counting identical items in a column and returning the occurrences

  • Follow


I use excell 2000. I have one column with 700 rows. In each cell is one 
number, either a 0, 1, 2, 3, 4, or 5. In the second column I have five rows, 
each with one number in increasing order: 0, 1, 2, 3, 4, 5. Next to the 
numbers 0-5 (in column three) I want the total number of occurances for each 
number in column one. So out of the 700 rows, if the number 2 occurred 230 
times, I want the number 230 to show in column 3 to correspond to the number 
2 in column two. Any ideas? 
0
Reply Utf 3/27/2010 1:19:01 AM

Try this:

In C1 (to count the zeros)

=COUNTIF($A$1:$A$701,"0")

In C2 (to count the 1s):

=COUNTIF($A$1:$A$701,"1")

etc.

Hope that helps.  If it does, rate it has helpful at the bottom of the post.

"ExcelWizardImNot" wrote:

> I use excell 2000. I have one column with 700 rows. In each cell is one 
> number, either a 0, 1, 2, 3, 4, or 5. In the second column I have five rows, 
> each with one number in increasing order: 0, 1, 2, 3, 4, 5. Next to the 
> numbers 0-5 (in column three) I want the total number of occurances for each 
> number in column one. So out of the 700 rows, if the number 2 occurred 230 
> times, I want the number 230 to show in column 3 to correspond to the number 
> 2 in column two. Any ideas? 
0
Reply Utf 3/27/2010 1:55:01 AM

That did it. Thanks much.


"Huber57" wrote:

> Try this:
> 
> In C1 (to count the zeros)
> 
> =COUNTIF($A$1:$A$701,"0")
> 
> In C2 (to count the 1s):
> 
> =COUNTIF($A$1:$A$701,"1")
> 
> etc.
> 
> Hope that helps.  If it does, rate it has helpful at the bottom of the post.
> 
> "ExcelWizardImNot" wrote:
> 
> > I use excell 2000. I have one column with 700 rows. In each cell is one 
> > number, either a 0, 1, 2, 3, 4, or 5. In the second column I have five rows, 
> > each with one number in increasing order: 0, 1, 2, 3, 4, 5. Next to the 
> > numbers 0-5 (in column three) I want the total number of occurances for each 
> > number in column one. So out of the 700 rows, if the number 2 occurred 230 
> > times, I want the number 230 to show in column 3 to correspond to the number 
> > 2 in column two. Any ideas? 
0
Reply Utf 3/27/2010 2:38:01 AM

In order to avoid the correction of each and every formula - in your private 
case - I would suggest, in cell C1 to type the following formula and copy 
down till C6:
=COUNTIF($A$1:$A$700,Row()-1)
Micky


"ExcelWizardImNot" wrote:

> That did it. Thanks much.
> 
> 
> "Huber57" wrote:
> 
> > Try this:
> > 
> > In C1 (to count the zeros)
> > 
> > =COUNTIF($A$1:$A$701,"0")
> > 
> > In C2 (to count the 1s):
> > 
> > =COUNTIF($A$1:$A$701,"1")
> > 
> > etc.
> > 
> > Hope that helps.  If it does, rate it has helpful at the bottom of the post.
> > 
> > "ExcelWizardImNot" wrote:
> > 
> > > I use excell 2000. I have one column with 700 rows. In each cell is one 
> > > number, either a 0, 1, 2, 3, 4, or 5. In the second column I have five rows, 
> > > each with one number in increasing order: 0, 1, 2, 3, 4, 5. Next to the 
> > > numbers 0-5 (in column three) I want the total number of occurances for each 
> > > number in column one. So out of the 700 rows, if the number 2 occurred 230 
> > > times, I want the number 230 to show in column 3 to correspond to the number 
> > > 2 in column two. Any ideas? 
0
Reply Utf 3/27/2010 9:09:01 AM

3 Replies
769 Views

(page loaded in 0.031 seconds)


Reply: