"Count Unique Values" Formula

9.56K viewsFormulas and Functions
0

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.

0

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….

You are viewing 1 out of 12 answers, click here to view all answers.

Latest Questions