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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
One quick way to drive it out ... use a pivot table (PT)
Item < assume this is the col header
car
car
bike
Click to expand...
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 ..

---
 
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.
Click to expand...

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
 
Both of these recommendations were helpful and I'm going with the pivot
table. Thank you so much. This made my day!
 
Back
Top