 |
 | 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 / New Users / May 2008How can I count how many times different texts repeat in a column?
Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. Try this: =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  SignatureBiff Microsoft Excel MVP > Hey, > [quoted text clipped - 8 lines] > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > repeats zero times it also is counted once... etc. When I copied this over to the cell, I was told t hat there were not enough arguments in the function > Try this: > [quoted text clipped - 12 lines] > > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > > repeats zero times it also is counted once... etc. In that case, you have mis-copied Biff's formula. Are you missing the comma or does your system require a semi-colon, not a comma? Gord Dibben MS Excel MVP >When I copied this over to the cell, I was told t hat there were not enough >arguments in the function [quoted text clipped - 15 lines] >> > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark >> > repeats zero times it also is counted once... etc. you are right. I got it to work now. Thank you very much. One more thing, though: I need to incorporate a couple of criteria into this formula. In a separate column is written whether or not these corporations are Non Profit or For Profit. And again in another is listed what program they are applying for. The non profit or for profit status is listed as text, but the program listings are shown as numerical requests in seperate columns (i.e. if they are applying for SAIL, then in the Sail column is a $ amount; for HOME, then in the HOME column there is a $ amount...). How do I build on this formula to specify how many For Profit corporations there are? and then how do I specify how many For Profit corporations there are applying for each program? Thank you for the help, pjr > In that case, you have mis-copied Biff's formula. > [quoted text clipped - 21 lines] > >> > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > >> > repeats zero times it also is counted once... etc. Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai > Try this: > [quoted text clipped - 12 lines] > > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > > repeats zero times it also is counted once... etc. If you can download and install the Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, >65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! SignatureBiff Microsoft Excel MVP > Hi. I had the same problemas pjr. Tried This formula and it worked for a > small subset of my data. When I try it on the full 70 000 entries my [quoted text clipped - 20 lines] >> > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark >> > repeats zero times it also is counted once... etc. Thanks > If you can download and install the Morefunc.xll add-in from: > [quoted text clipped - 33 lines] > >> > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > >> > repeats zero times it also is counted once... etc. You're welcome! SignatureBiff Microsoft Excel MVP > Thanks > [quoted text clipped - 38 lines] >> >> > Landmark >> >> > repeats zero times it also is counted once... etc. I download and installed the add-in. The COUNTDIFF function works for 2003 but doesn't seem to be in 2007 at all. I'm looking under the formula tab as well as typing =COUNTDIFF into the formula bar with no success. Am I just missing something obvious here? Thanks for your help so far. > If you can download and install the Morefunc.xll add-in from: > [quoted text clipped - 33 lines] > >> > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > >> > repeats zero times it also is counted once... etc. Are you sure the add-in is installed in Excel 2007? If you goto the Formulas tab there should be a group called Morefunc. This is the eqivalent of the Insert Function button on the formula bar. You can also use the Insert Function button on the formula bar. From the category drop down select Morefunc. If you type in the formula and got a #NAME? error then that means the add-in isn't installed. SignatureBiff Microsoft Excel MVP >I download and installed the add-in. The COUNTDIFF function works for 2003 > but doesn't seem to be in 2007 at all. I'm looking under the formula tab [quoted text clipped - 42 lines] >> >> > Landmark >> >> > repeats zero times it also is counted once... etc. Try this: =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hey, > [quoted text clipped - 8 lines] > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > repeats zero times it also is counted once... etc. Hi Select the column > "Data" > "Filter" > "Advanced Filter" > Check "Unique records only" > OK In the bottom of the screen you will see the number of Unique records. To show all records again > Data > Filter > Show All Regards, Per > Hey, > [quoted text clipped - 8 lines] > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > repeats zero times it also is counted once... etc. This did exactly what I'm looking to do, but I need it to be done in a cell, as a function, on a separate worksheet. > Hi > [quoted text clipped - 20 lines] > > i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark > > repeats zero times it also is counted once... etc. |
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
|
©2008 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.