"Count Unique Values" Formula
I am looking for a way to count the unique occurrences in a list. For example, if the list of cell values is…
1,1,5,5,9,9
… then the formula should return “3” since there are three unique values in the list.
You can do this in Excel using the SUMPRODUCT and COUNTIF functions together. For instance, in a range of values from cells A1 to A10, the following formula counts the number of unique occurrences:
=SUMPRODUCT((A1:A10<>””) / (COUNTIF(A1:A10, A1:A10&””)))
(Note that the “” constructions are used to ignore blanks in the list, which would cause the formula to return an error).
I’ve tried several ways of replicating this formula in Quantrix with no success. You can use multiple columns to achieve the result in Quantrix, but I need a solution that involves only one formula.
Mike,
Thanks!
I have attached a new version of the plugin including the source code, the Quantrix model for testing and a screenshot.
The new function CountUniqueItems() can not only be used for just one column it can be applied to several columns. See the screen shot for more information.
Please test this new function extensively as I have tested it just on my computer.
Any feedback is much appreciated.
Dominik
Hi Bryan,
Many thanks for your hint!
I think that’s new in Eclipse 3.3 that the developer has to tick the plugin.xml file. It is working on my computer now.
I will add the plugin, the source and a Quantrix test file including the instructions later today. Further, based on the experiences I did during the development of this function I will update the Function Development tutorial published at the beginning of this year.
Could this function be added to the v3.1 release?
Dominik
Mike,
Because the com.quantrix.function.countuniqueitems_1.0.0.jar seems not to be recognized by the Quantrix Modeler I have attached the whole Eclipse Java Plug-in development project as a zip file to this post.
It would be great if one of the Quantrix developers could take a quick look at it to find the issue. Many thanks!
Dominik
J Scott,
Mike,
I have written a first prototype of the new function CountUniqueItems. It works for numbers only.
The new function is named CountUniqueItems it is added below the category ‘Statistical’.
You can find the com.quantrix.function.countuniqueitems_1.0.0.jar file (place it in your ..Quantrix Modelerplugins folder) and the testCountUniqueItems.model file for testing in the attached ZIP file.
Unfortunately, the plugin runs in the test mode only when running it from the Eclipse IDE on my machine. If I export the plugin and run it with the Quantrix Modeler it does not show up in the category ‘Statistical’. You might have more luck.
Just let me know if it works on your machine. More on this tomorrow (including the source code).
Dominik
Thank you for the additional information. After analysis, it looks as though the current implementation of our countif function will not allow for single-formula answer. One would have to create a new column to get the answer. We will probably address this in a future release of Quantrix Modeler.
Dom – if you want to write a QAPI function – please feel free. I am sure the Quantrix community would find it useful.
-Mike
Attached is an Excel 2003 spreadsheet that does a unique count. I wonder if the formulas behave differently in different versions? (I don’t have 2007).
I also attached a Quantrix model that does the same thing by using a separate column.
Also for those interested, here is an explanation of why this works in Excel:
=SUMPRODUCT((A1:A10<>””) / (COUNTIF(A1:A10, A1:A10&””)))
The formula aims to find unique values by boiling all like rows down into a value of “1”. For instance, if the letter A occurs four times in the list, then we can think of each A as 25% of all A’s; therefore, the total percent for all A’s will be 100%. If you do this for each row you will sum to a 1 for each unique occurrence.
This works in Excel because SUMPRODUCT performs a math operation on each row individually, then sums the result. In this case, we want to examine each cell, divide it by the number of times it occurs in the list (the COUNTIF portion), and sum the result.
The <>”” construction removes any rows that contain blank cells, which would cause a divide by zero error.
I misread your first question. I was thinking you were looking for a unique rank. Anyway….
If you don’t mind could you post a small example of this working in a spreadsheet? Your instructions are quite clear but I am having trouble replicating the countif portion of the formula in a spreadsheet. It keeps returning zero for me.
Once I get a working copy in a spreadsheet, we can make an attempt to get a Quantrix equivalent function.
Thanks.
-Mike
Mike,
This kind of setup does indeed solve the uniqueness problem. However, it requires that you add a column to a dimension (like the Rank column in your example).
The matrix I am dealing with is huge and adding a second column to each existing column would double the size of the matrix. I am hoping to accomplish the unique count without using any additional columns – it needs to be completely contained within the count formula.
It may be impossible….