Count Instances
 

How can I count the number of times each of numerous
items appears in a column? That is, I have a column

a
a
b
b
b
c
c
c
c
d
d
etc.

Each entry represented by a letter above is in fact
several words. There are about 10,000 rows in the
table. I would like result like

a 2
b 3
c 4
d 2
etc.

Thanks in advance.

Ken

You can use the countif function, say for example you wanted to find the
number of occurances of the letter a in the column (which is column A)

=COUNTIF(A1:A10000,"a")


If you have many entries you could have a table of the occurances you are
looking for and instead of "a" you reference that cell.

Alternatively you could create a Pivot Table, Data PivotTable and
PivotChart Report. Select Excel list, select the data range, click layout,
from the fields on the right drag the column heading to the left side of the
table, and then drag to the middle and double click that field and click
count. Click OK and decide where you want the table. The result will be table
giving you a count of all the unique occurances. To update the table as you
add data simply right click, refresh data. Make sure that your column are
properly labelled.

Regards


"Ken" wrote:

How can I count the number of times each of numerous
items appears in a column? That is, I have a column

a
a
b
b
b
c
c
c
c
d
d
etc.

Each entry represented by a letter above is in fact
several words. There are about 10,000 rows in the
table. I would like result like

a 2
b 3
c 4
d 2
etc.

Thanks in advance.

Ken

If your data is sorted (and has headers), you could use Data|subtotals and hide
the detail rows.

But data|pivottable is very nice for this.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Ken wrote:

How can I count the number of times each of numerous
items appears in a column? That is, I have a column

a
a
b
b
b
c
c
c
c
d
d
etc.

Each entry represented by a letter above is in fact
several words. There are about 10,000 rows in the
table. I would like result like

a 2
b 3
c 4
d 2
etc.

Thanks in advance.

Ken

--

Dave Peterson