 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / December 2005Counting # of Times an Item Shows Up in a Table
Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks Have you tried the COUNTIF function? > Hi, > [quoted text clipped - 8 lines] > > Thanks Well, with that function I'd have to specify WHAT I want it to count such as a function for dog, one for cat, etc... I just want excel to compile a list of everything that was in the table with the # of times it appears in the table without having to write a function for each entry, you understand? have a suggestion? THANKS! > Have you tried the COUNTIF function? > [quoted text clipped - 10 lines] > > > > Thanks Maybe not exactly what you're looking for, but ... You could enter this formula in the column adjacent to the datalist, which will simply display the count of every item in the table: =COUNTIF($A$1:$A$100,A1) And copy down. Then, this formula will give you a count of the unique items in the table: =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks i cant seem to get these formulas to work, but i'm very excited that i think there may be a solution here, is it possible that i could email the file to you and you could show me how to arrange those formulas??? or can I post the file somehow to this discussion group?? > Maybe not exactly what you're looking for, but ... > [quoted text clipped - 20 lines] > > Thanks Can you create a list of unique items, and enter them in a column? SignatureRegards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- i cant seem to get these formulas to work, but i'm very excited that i think there may be a solution here, is it possible that i could email the file to you and you could show me how to arrange those formulas??? or can I post the file somehow to this discussion group?? "RagDyeR" wrote: > Maybe not exactly what you're looking for, but ... > [quoted text clipped - 20 lines] > > Thanks B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) as an array formula, commit with Ctrl-Shift-Enter, and copy down C1: = COUNTIF(A:A,B1) and copy down SignatureHTH RP (remove nothere from the email address if mailing direct) > Hi, > [quoted text clipped - 7 lines] > > Thanks I am having the same difficulty as with RagDyeR's post which is exactly where to put these formulas. My table is from A1 to G11 so where do I put those formulas Bob? Sorry, I'm a little Excel challenged. The easiest way to communicate all this is if I could somehow post the file to you. Is that possible? Thanks > B1: = A1 > B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"", [quoted text clipped - 19 lines] > > > > Thanks I tell you exactly where to put the formulae, B1, B2 and copy down, C1 and copy down SignatureHTH RP (remove nothere from the email address if mailing direct) > I am having the same difficulty as with RagDyeR's post which is exactly where > to put these formulas. My table is from A1 to G11 so where do I put those [quoted text clipped - 6 lines] > > B1: = A1 > > B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2 > > 0&""),0))) > > [quoted text clipped - 16 lines] > > > > > > Thanks How about a Pivot Table? Select your data, then Data > Pivot Table. When you get to the layout, drag the column header for items into the Rows field AND into the data field. Check the label on the item in the data field; if is isn't 'Count of xxxx', double-click it and change the 'summarize' function to Count. Caveat regarding Pivot Tables: if your data changes, the Pivot won't refresh automatically; right-click the table and select Refresh Data. > Hi, > [quoted text clipped - 7 lines] > > Thanks I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") Data>Pivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro > Hi, > [quoted text clipped - 7 lines] > > Thanks ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ > I know you posted this in the Worksheet Functions forum, but I think a Pivot > Table might give you what you're looking for more easily: [quoted text clipped - 39 lines] > > > > Thanks ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ > I know you posted this in the Worksheet Functions forum, but I think a Pivot > Table might give you what you're looking for more easily: [quoted text clipped - 39 lines] > > > > Thanks >>field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro > ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe > I'm too excel challenged but I'm finding your instructions a little too [quoted text clipped - 47 lines] > > > > > > Thanks yikes, Sorry Ron but I really really appreciate your help :) > >>field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, > bird"<< [quoted text clipped - 63 lines] > > > > > > > > Thanks Here's a possibility....if you have a list of the possible values that could be in the list. Assuming the list is in Cells A1:A30 B1: dog B2: cat etc C1: =SUMPRODUCT(LEN($A$1:$A$30)-LEN(SUBSTITUTE(UPPER($A$1:$A$30),UPPER(B1),"")))/LEN(B1) Copy that formula down as far as you need That formula returns the number of times the text in col_B appears in Col_A. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro > yikes, Sorry Ron but I really really appreciate your help :) > [quoted text clipped - 65 lines] > > > > > > > > > > Thanks thanks Ron, but not really, no, because I don't have a "a list of the possible values that could be in the list" in any organized way, the information is scattered throughout the table, any other ideas? > Here's a possibility....if you have a list of the possible values that could > be in the list. [quoted text clipped - 88 lines] > > > > > > > > > > > > Thanks also, if I can email the file let me know, I think it'll make things a lot easier, THANKS!!!!! > >>field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, > bird"<< [quoted text clipped - 63 lines] > > > > > > > > Thanks well .. think you could upload a small sample copy of your file via a free filehost, and then include a *link* to it in your response here for those interested to take a look at your file. This would also help to keep discussions open to all within the newsgroup, for the benefit of all. Some free filehosts that could be used: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php For cjoint.com (it's in French), just click "Browse" button, navigate to folder > select the file > Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your query. Note that you should *not* post any attachments direct to the newsgroup -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- > Hi, > [quoted text clipped - 7 lines] > > Thanks You might be looking for something like the one my post describes in: http://www.mrexcel.com/board2/viewtopic.php?t=115570 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.