ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   counting identical items in a column and returning the occurrences (https://www.excelbanter.com/excel-discussion-misc-queries/260043-counting-identical-items-column-returning-occurrences.html)

 ExcelWizardImNot March 27th 10 02:19 AM

counting identical items in a column and returning the occurrences

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?

 Huber57 March 27th 10 02:55 AM

counting identical items in a column and returning the occurrences

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?

 ExcelWizardImNot March 27th 10 03:38 AM

counting identical items in a column and returning the occurre

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?

 מיכאל (מיקי) אבידן March 27th 10 10:09 AM

counting identical items in a column and returning the occurre

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?

 All times are GMT +1. The time now is 06:16 PM.