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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BikrChic - 20 Sep 2007 02:12 GMT

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.

Reply to this Message

Max - 20 Sep 2007 02:48 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 18 lines]
>
> Any advice is greatly appreciated.

Reply to this Message

BikrChic - 20 Sep 2007 14:32 GMT

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

> One quick way to drive it out ... use a pivot table (PT)
>
[quoted text clipped - 36 lines]
> >
> > Any advice is greatly appreciated.

Reply to this Message

Max - 20 Sep 2007 15:04 GMT

welcome. glad to hear that.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

JW - 20 Sep 2007 05:31 GMT

On Sep 19, 9:12 pm, BikrChic <BikrC...@discussions.microsoft.com>
wrote:

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

Reply to this Message