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.
0
BikrChic (2)
9/20/2007 1:12:02 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
671 Views

Similar Articles

[PageSpeed] 6

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.
0
demechanik (4694)
9/20/2007 1:48:00 AM
On Sep 19, 9:12 pm, BikrChic <BikrC...@discussions.microsoft.com>
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

0
JWRIGHT50 (29)
9/20/2007 4:31:13 AM
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.
0
BikrChic (2)
9/20/2007 1:32:03 PM
welcome. glad to hear that.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"BikrChic" <BikrChic@discussions.microsoft.com> wrote in message 
news:90CE0BFC-826B-46CE-AD70-CCE4657AA362@microsoft.com...
> Both of these recommendations were helpful and I'm going with the pivot
> table. Thank you so much. This made my day!


0
demechanik (4694)
9/20/2007 2:04:58 PM
Reply:

Similar Artilces:

Counting half days holiday and sickness in excel
Hi I have setup the formula to count H for holiday and S for sickness within excel but if i have half a day i can not do it can someone help please? Thanks Wane Use H for a full day, h for a half day, and then use =SUMPRODUCT(EXACT(C2:M2,{"H";"h"})*{1;0.5}) if the data goes across columns. If it goes down rows, use =SUMPRODUCT(EXACT(B2:B20,{"H","h"})*({1,0.5})) -- __________________________________ HTH Bob "Wayne" <Wayne@discussions.microsoft.com> wrote in message news:D330E17E-BE64-407D-83E9-3EAD67F0024E@microsoft.com... >...

Copy selected columns from .xls to a .txt file question
I would like to be able to copy two columns from a .xls file, columns 2 (B) and 9(I) into a .txt file for approximately 100 rows, using VBA. What is the best way to do this? Thank you try this Sub Copy_Rows() With Application .DisplayAlerts =3D False .ScreenUpdating =3D False End With FpatH =3D "C:\Documents and Settings\username\filename.xls" Workbooks.Open FpatH FileP =3D ActiveWorkbook.path Range("B1:C100").Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:=3D _ FileP & "\" & "1.txt&...

Counting Distinct Dates with Criteria
I need to count the number of days that correspond to two other criteria. The following formula will count the distinct number of dates: =SUMPRODUCT(--(B605:B3000<>"")/COUNTIF(B605:B3000,B605:B3000&"")) But if I put that into a formula like the following it doesn't do what I want: =SUMPRODUCT(--(H605:H3000=B9),--(G605:G3000=C9),--(B605:B3000<>"")/COUNTIF(B605:B3000,B605:B3000&"")) 10/17 ven x 10/17 ven x 10/18 ven y 10/19 ven z 10/19 ven x 10/20 ven x 10/20 ven x 10/20 ven x 10/20 ven z Here'...

Can I have more than 256 columns on my spreadsheet?
I'm very much a newbie to excel. I'd like to setup a spreadsheet with more than 256 columns. Can this be done? If so, how? Hi no, not possible with Excel -- Regards Frank Kabel Frankfurt, Germany RML51 wrote: > I'm very much a newbie to excel. > > I'd like to setup a spreadsheet with more than 256 columns. Can this > be done? If so, how? Bummer - Thanks "Frank Kabel" <frank.kabel@freenet.de> wrote in message news:%23ZDzv7f4EHA.3420@TK2MSFTNGP10.phx.gbl... > Hi > no, not possible with Excel > > -- > Regards > Frank Kabel ...

eConnect Issues For Invoicing Taxable Items
I have an ASP.NET web application that I am integrating with a GP 10 backend. Namely, a user can place an order online and after some processing an invoice gets added to GP. I have successfully integrated one part of the application where there are no taxes involved, but am having issues creating an invoice that includes taxes. Here is the error message that I'm getting: Tax table detail does not equal the tax amount Node Identifier Parameters: taSopHdrIvcInsert The invoice contains two line items, one is taxable and one is not. For the first line item I specify: UNITPRCE...

Update Promised Date on Item Quantities Maintenance
I don't know why, but when the Promised Date is changed on a Purchase Order, the Promised Date displayed on the Item Quantities Maintenance screen does not get updated. This date is important for our salespeople to see to let customers know when we are expecting certain items in, and it should always be up to date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sug...

Product Suggestion
We need to maintain Item Shipping Weights with a wider range of values for items than currently possible in GP. We sell both small items (resistors, fractions of a kg) and big ones (drums of cable). It is not currently possible to store both extremes because the field is currently an integer fixed at 2 decimals. It really needs to be a numeric field with greater precision and scale. This is particularly important in EU because of the introduction of WEEE directive on recycling waste, which has big implications in this area. ---------------- This post is a suggestion for Microsoft, and ...

View Problems
Hi, We've got an installation of MS CRM 1.2 which is mostly working correctly however we have a proplem with the column header text not displaying in views when viewing the lists of accounts/contacts/leads etc.. The headers are there but without any text and if we click on one to sort the list by that column, the following error is given: Line: 134 Char: 2 Error: 'style' is null or not an object Code: 0 URL: http://crmserver/_grid/behaviours/grid.htc Also, when we try to create a custom view and click on 'add fields' we get the following error: Line: 126 Char: 5 Error:...

Outlook 2003 Rules Problem with Calendar Items (bug?)
To support a product trial, we are in the position of needing to maintain two Exchange worlds, one at Exchange 5.5 and the other at Exchange 2000 (which will be upped to 2003 next week). Trial users have accounts and mailboxes on both systems. The two systems are connected together for mailflow, as well as dirsync using ADC and Free/Busy sync using the inter-org PF replicator. All Outlook clients are Outlook 2003. Outlook 2003 has increased the power of rules, in particular there are now rules to handle calendar items. So we are trying to use these to sync a user's Exchange 200...

Can't group pivot table items by month in Excel
i use excel 2004 for mac but have the same problem using 2003 for windows. i have a pivot table with dates in the rows, names in the columns and count of quantity in the data section. i want to group the date entries by month. i follow microsoft's help directions listed below but i am never prompted with a starting at box, ending at box or by box. the pivot table just groups whatever i have highlighted and gives me no other options. i would like to use this smart grouping option. please help. 1. Hold down CONTROL and click the selected date or time items, point to Group and Outline...

Sorting Multiple Columns in Excel 2003
I have this: 1 4 3 5 8 6 I want this: 1 3 4 5 6 8 Any Ideas? This is in Excel 2003. EggHeadCafe - Software Developer Portal of Choice WPF DataGrid Custom Paging and Sorting http://www.eggheadcafe.com/tutorials/aspnet/8a2ea78b-f1e3-45b4-93ef-32b2d802ae17/wpf-datagrid-custom-pagin.aspx To clarify: I have two colums. Each has some blanks and some numbers. I want the two colums sorted ascending taking into consideration the numbers in both and relative to both. Jason Perilla wrote: Sorting Multiple Columns in Excel 2003 29-Sep-09 I have this: 1 ...

Recovering a file in Word 2007
I have been composing a blog entry on my new HP Mini computer. I worked several days on the document and several hours yesterday. I physically saved (pressed the save button) when I finished working on it yesterday. This morning I only have the material I wrote on the 13^th ..nothing is left from yesterday.! I'm using a trial version of MS Word 2007 and I have MS XP Opsys.. Any hints on recovering it? I've tried the typical search function and only get the old doc. LP -- lfpevec ------------------------------------------------------------------------ lfpevec'...

Issues with new document in Word 2007
The user has two documents open in Word 2007. He is copying and pasting from an existing document to a new one. Here are the problems that he is having with the "new" document: 1) If he selects one word to make Bold then ONLY that word will be affected. However, if the user chooses more than one word to Bold then the entire page becomes Bold. The user will then utilize the "Undo" feature and what he originally wanted to be Bold turns out that way. 2) Bullet points: The user will place his cursor next to a sentence, click on the Bullet point option, ...

Subquery for column names
I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a ...

Convert a Word rtf file into excel
Hi, I am trying to get data out of an rtf file into excel, however it is putting all information into 1 column,... Anyway to spread the data across multiple columns just like it looks in the rtf file? Please let me know. Thanks, Joan Open the rtf file in WordPad and save it as txt. Open Excel and then from Excel: Data > Open External Data... > Open Data and the submit the filename Tell the Import Wizard how to separate the fields. -- Gary''s Student - gsnu200767 "colorado808" wrote: > Hi, > > I am trying to get data out of an rtf file into exce...

Export rows in Excel to lutiple Word files
I have an excel (or access if easier) doc that contains rows of data. I would like to create a separate word doc from each row. The clomuns are subject,number,info I would love the title of each to be "<Number>-<Subject>" with the content of the doc itself made up from the Info row. Is there a way to do this - I know a bit of perl but was hoping for an easy solution....... Try this. Select the row Copy to the clip board Switch to MS Word Use either Paste to paste the clipboard contents as a table or Paste Special with the Link Option selected. Perhaps specify as...

Excel cell filler for word merge document
I am using Excel as a database for letter mail merge to Word. I have "first name", "middle initial", "last name" fiels in the merge document. Not all ppl have middle initials so Word is inserting an extra space in those letters. Is there a filler that I can put in those cells with no initial to keep Word from inserting that extra space? Thank you in advance for any suggestions. Shawna I found this, but do not know how to implement it: The following conditional MERGEFIELD fields remove the blank space if the middle initial field is blank. For example, giv...

How to finish editing item of CListCtrl?
Using CListCtrl::EditLabel function, edit window is created. It disappears when "Enter" or "ESC" key is pressed. How can I finish it by code? Are there any method or function to finish it? "Joon-ho Ryu" <junoryu@hotmail.com> wrote in message news:%23qBXb1IbDHA.1384@TK2MSFTNGP10.phx.gbl... > Using CListCtrl::EditLabel function, edit window is created. > It disappears when "Enter" or "ESC" key is pressed. > How can I finish it by code? > Are there any method or function to finish it? FWIW, the documentation says "To can...

how to import word 2007 custom setting from one computer to anothe
I have a lot of custom settings in Word 2007 on one computer. I am installing word on another computer and want to import all my settings--toolbar, dictionaries, macros, etc. How do I do this. I have info on how to do the auto-correct entries, but haven't found info on how to do the others. thanks. also, I am wondering why even tho I check the "notify me of replies" box on this site, I never get notified of replies. ...

Mail Format (Use Microsoft Word to edit e-mail messages)
I've recently installed Outlook 2002 and when I'm trying to send a e-mail with Excel, Word ect by doing FILE SEND TO I've notice that th Mail Recipient is grey out. When I look in Outlook in Mail Format I'v notice that Use Microsoft Word to edit e-mail messages is grey out What can I do to enable this option? Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

column names
Another user of an excel sheet I created has changed the column names from the alphabetic ones to numbers making the transfer of cells by thier ref. difficultt . Instead of reading ref A1 for example it shows R1C1. Any ideas how i can change it back - have searched help with no success Go into Tools/Options/General and uncheck the R1C1 reference style box Best rgds Chris Lav "genie" <genie@discussions.microsoft.com> wrote in message news:A88EC58E-39A2-420F-B6D9-D6E412B65DFC@microsoft.com... > Another user of an excel sheet I created has changed the column names fro...

Need to unhide 2 columns that I joined together
I pressed alt and joined columns B & C - now I can't unhide them both -help - I am working under a big time contraint. "Divertada" <Divertada@discussions.microsoft.com> wrote in message news:233CED6E-23D3-43BD-8FBA-A3DDCA4D2355@microsoft.com... > I pressed alt and joined columns B & C - now I can't unhide them both -help - > I am working under a big time contraint. I'm not sure what you mean by "joined together" but, to normally to unhide columns you would select cells spanning the hidden column (in this case eg A1 to D1) then go Format>...

page count Word 2007
When I create a document at home and send it to the office, it prints out the doc plus 28 blank pages. I opended the last doc I created and looked in the bottom corner as someone suggested I do, and it said page count 1 0f 29. I clicked on it to see if I could change it, and now it has disappeared, all that shows is word count. I have limited knowledge, so would appreciate step by step help, if possible. Thanks in advance. Hi Bev, If you're not already doing so, I suggest: 1. switching to 'Print Layout' view, so that you can see the actual page structure; and 2. to...

how to display the count of lines found by autofilter?
Using autofilter to filter rows in a range used to give a count of the number of rows found in the bottom left hand of the screen. Now I have excel 2003 that very useful feature does not seem to be there. Is this a setting I have not found? Is there anyway of getting it back? It's still there, and it still disappears every once in a while. Debra Dalgleish has some workarounds at: http://www.contextures.com/xlautofilter02.html#Count elaineb wrote: > > Using autofilter to filter rows in a range used to give a count of the number > of rows found in the bottom left hand of...

Include vendos items cost in receipt window
Is there a way to include in the Line-Item of the Purchase Receipt Window a field where we can include what the Vendor actually invoice per line item? We don't want to change the Receipt Window "Unit Cost", we would like to add what the Vendor actually invoice for that Item. Are you actually wanting to enter the invoice amount at this time? The Shipment Invoice transaction allows this. In either the Shipment or the Shipment Invoice transaction, the unit price displayed is from the PO. The invoice amount would be entered on the Shipment/Invoice transaction to get the cor...