| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
| How do I count words in a column if they are inexact? | Excel Worksheet Functions | |||
| How do I sort - and count - items in a column? | Excel Discussion (Misc queries) | |||
| How do I count the items in one column if another column is blank | Excel Worksheet Functions | |||
| Count number of items in one column that have a value in another? | Excel Worksheet Functions | |||
Hybrid Mode
