 |
 | 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 / March 2008Formula to count unique values in a range
Is there a formula that will count the number of unique values in a given range. In this instance, my range has both letters and numbers. For example. A234 A234 A998 A961 I need a formula that will count the above and produce the number 3. Thanks in advance for any suggestions. Try this: =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Is there a formula that will count the number of unique values in a > given range. [quoted text clipped - 11 lines] > > Thanks in advance for any suggestions. Hello, I know that this worksheet function solution is almost common sense here. It has been suggested quite often. But: Its runtime is acceptable only for a small amount of rows. If you copy my UDF lfreq (http://www.sulprobil.com/html/listfreq.html) you could count unique entries with =ROWS(lfreq(A1:A10)) For 10 rows FastExcel shows these runtimes: SUMPRODUCT/COUNTIF 0.12 msec ROWS/lfreq 2 msec For 100 rows: SUMPRODUCT/COUNTIF 1.2 msec ROWS/lfreq 3.5 msec For 1000 rows: SUMPRODUCT/COUNTIF 85 msec ROWS/lfreq 15 msec Now for 10,000 rows we get: SUMPRODUCT/COUNTIF 8,400.00 msec ROWS/lfreq 125 msec Meaning: You will wait 8 seconds for the answer of ONE single cell results with the SUMPRODUCT/COUNTIF approach for 10,000 rows. Regards, Bernd Here's what I did... I filled A1:A10000 with the numbers 1 : 10000 =SUMPRODUCT((A1:A10000<>"")/COUNTIF(A1:A10000,A1:A10000&"")) >You will wait 8 seconds for the answer On my machine, which is basically gutless by "todays" standards, it took on average 5.3 secs measured using Charles Williams' rangetimer method. It took on average *46* secs using the UDF and formula =ROWS(lfreq(A1:A10000)) screencap: http://img517.imageshack.us/img517/3247/lfreqhj2.jpg  SignatureBiff Microsoft Excel MVP > Hello, > [quoted text clipped - 28 lines] > Regards, > Bernd Hi Biff, Interesting. Since the SUMPRODUCT/COUNTIF approach takes quadratic runtime and the ROWS(lfreq) only linear, there has be to be a cut-off/break even point, I believe. I think the "repeat"-ratio determines this cut-off-point. Your test data does not show any duplicates, right? Regards, Bernd >Your test data does not show any duplicates, right? Right, no duplicates. 10,000 unique entries which the formula correctly calculates. The rangetimer routine is available here: http://msdn2.microsoft.com/en-us/library/aa730921.aspx It's probably the same routine used in FastExcel. SignatureBiff Microsoft Excel MVP > Hi Biff, > [quoted text clipped - 10 lines] > Regards, > Bernd Hello again, IMHO Charles Williams' UDF countu should be taken: http://msdn2.microsoft.com/en-us/library/aa730921.aspx Regards, Bernd Not sure if you've done a typo here or I'm missing something, but I can only see two unique values, ie A234. If that's the case try =COUNTIF(B1:B100,"A234") Or if you want to put A234 in a cell, say E1. COUNTIF(B1:B100,E1) Change the ranges to suit, Regards, Alan. > Is there a formula that will count the number of unique values in a > given range. [quoted text clipped - 11 lines] > > Thanks in advance for any suggestions. There are 3 unique values in the range: A234, A998, A961. Ron's formula returns 3. Perhaps you're confusing unique and duplicate? Tyro > Not sure if you've done a typo here or I'm missing something, but I can > only see two unique values, ie A234. If that's the case try [quoted text clipped - 19 lines] >> >> Thanks in advance for any suggestions. You mean 9? If that's whats required then great. I must be getting old! Odd way to ask the question but if the end result is what's needed then I bow!!!! Regards, Alan > There are 3 unique values in the range: A234, A998, A961. Ron's formula > returns 3. Perhaps you're confusing unique and duplicate? [quoted text clipped - 24 lines] >>> >>> Thanks in advance for any suggestions. There are 4 values in the column. A234, A234, A998, A961. Of the 4 values, 3 are unique: A234, A998 and A961. There is 1 duplicate: A234. The OP asked how to compute the number of unique entries. Ron's formula returns 3 which is correct. I have no idea where you get 9 from. Tyro > You mean 9? If that's whats required then great. I must be getting old! > Odd way to ask the question but if the end result is what's needed then I [quoted text clipped - 29 lines] >>>> >>>> Thanks in advance for any suggestions. Of course, obtuse thinking on my part, apologies, Alan. > There are 4 values in the column. A234, A234, A998, A961. Of the 4 > values, 3 are unique: A234, A998 and A961. There is 1 duplicate: A234. [quoted text clipped - 36 lines] >>>>> >>>>> Thanks in advance for any suggestions.
|
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.