"Count Unique Values" Formula

9.56K viewsFormulas and Functions

I am looking for a way to count the unique occurrences in a list. For example, if the list of cell values is…


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


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.

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

Latest Questions