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

[Guest]

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.

 

[Guest]

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 ..

---

 

[JW]

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

 

[Guest]

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

 

[Max]

welcome. glad to hear that.