How do I count like items (words) in a column?
 

This is probably the dumbest question a person can ask about Excel but I'm
running out of time and it's making me crazy. I have a column with about 20
different items (words not numbers) and some of the items are listed more
than once. All I want to do is create a column that has the total number of
each item in the list. For example:

Item

car
car
car
bike
bike
bike
bike
bike

Where the cars equal 3 and the bikes equal 5 and these numbers are listed
next to the respective item. My goal is to reduce the steps since I have to
do calculate about 20 items for about 30 people.

Any advice is greatly appreciated.

How do I count like items (words) in a column?
 

One quick way to drive it out ... use a pivot table (PT)

Item < assume this is the col header
car
car
bike
etc

Select the col
Click Data Pivot table
Click Next Next
In step 3 of the wiz. click Layout
Drag n drop Item within the ROW area
Drag n drop Item within the DATA area
(It'll appear as Count of Item)
Click OK Finish. That's it.

Hop over to the PT sheet for the results ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BikrChic" wrote:
This is probably the dumbest question a person can ask about Excel but I'm
running out of time and it's making me crazy. I have a column with about 20
different items (words not numbers) and some of the items are listed more
than once. All I want to do is create a column that has the total number of
each item in the list. For example:

Item

car
car
car
bike
bike
bike
bike
bike

Where the cars equal 3 and the bikes equal 5 and these numbers are listed
next to the respective item. My goal is to reduce the steps since I have to
do calculate about 20 items for about 30 people.

Any advice is greatly appreciated.

How do I count like items (words) in a column?
 

On Sep 19, 9:12 pm, BikrChic
wrote:
This is probably the dumbest question a person can ask about Excel but I'm
running out of time and it's making me crazy. I have a column with about 20
different items (words not numbers) and some of the items are listed more
than once. All I want to do is create a column that has the total number of
each item in the list. For example:

Item

car
car
car
bike
bike
bike
bike
bike

Where the cars equal 3 and the bikes equal 5 and these numbers are listed
next to the respective item. My goal is to reduce the steps since I have to
do calculate about 20 items for about 30 people.

Any advice is greatly appreciated.

Like Max said, a Pivot Table could be used. Or select the column and:
Go to Data--Filter--Advanced Filter
Select Copy To Another Location
Check Unique Values
In the Copy To field, click on a cell or column that contains no data
Click OK.

This will create a list of all of the unique items. You can then use
a CountIf formula in the cell next to each value.
=CountIf(A:A,C2)

The above formula will count the number of times the value in C2
appears in column A.

HTH

How do I count like items (words) in a column?
 

Both of these recommendations were helpful and I'm going with the pivot
table. Thank you so much. This made my day!

"Max" wrote:

One quick way to drive it out ... use a pivot table (PT)

Item < assume this is the col header
car
car
bike
etc

Select the col
Click Data Pivot table
Click Next Next
In step 3 of the wiz. click Layout
Drag n drop Item within the ROW area
Drag n drop Item within the DATA area
(It'll appear as Count of Item)
Click OK Finish. That's it.

Hop over to the PT sheet for the results ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BikrChic" wrote:
This is probably the dumbest question a person can ask about Excel but I'm
running out of time and it's making me crazy. I have a column with about 20
different items (words not numbers) and some of the items are listed more
than once. All I want to do is create a column that has the total number of
each item in the list. For example:

Item

car
car
car
bike
bike
bike
bike
bike

Where the cars equal 3 and the bikes equal 5 and these numbers are listed
next to the respective item. My goal is to reduce the steps since I have to
do calculate about 20 items for about 30 people.

Any advice is greatly appreciated.

How do I count like items (words) in a column?
 

welcome. glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BikrChic" wrote in message
...
Both of these recommendations were helpful and I'm going with the pivot
table. Thank you so much. This made my day!