How to count the number of unique values in a range in Excel

Hello, on many occasions we have a list or range with lots of data, and we wondered how many of those values or data are unique, and we have had to develop a complex solution by adding helper columns and doing multiple operations.

That is no longer necessary !!

Because we can calculate the number of unique values in a list with a simple formula.
And what formula is that ?? Let's see ...





Imagine you have the list of users who have received a SMS from Rachel on a particular day, and that list is in the range A1:A14.

And we want to know how many users have received at least one SMS from Rachel. The list includes a total of 14 SMS, but Rachel has sent 2 to Ingrid, James and Arthur. So a total of 11 unique users. So we can create a formula that returns the number of unique users.

= SUMPRODUCT(1/COUNTIF(A1:A14, A1:A14))

And how does this formula work??

To begin we will see what the COUNTIF does.

=COUNTIF(A1:A14, A1:A14)

This part is counting how many of each user are in the list. It is an array formula. It must be understood as follows:

=COUNTIF(A1, A1:A14)= 2

=COUNTIF(A2, A1:A14) = 1

....

=COUNTIF(A14, A1:A14) = 2

Bringing the result of this formula as an array.

=COUNTIF(A1:A14, A1:A14) = {2, 1, 2; 1; 1; 1; 2; 2; 1; 2; 1; 1; 1; 2}

To see the array, once in the cell press F2 + F9.

Thus:

1/COUNTIF(A1:A14, A1:A14) = {0.5; 1; 0.5; 1; 1; 1; 0.5; 0.5; 1; 0.5; 1; 1; 1; 0.5}

And therefore:

= SUMPRODUCT(1/COUNTIF(A1:A14, A1:A14)) = {0.5 + 1 + 0.5 + 1 + 1 + 1 + 0.5 + 0.5 + 0.5 + 1 + 1 + 1 + 1 + 0.5} = 11

Well we now know how to calculate how many unique values ​​are in a list. I hope you enjoyed this trick.

Related links

SUMPRODUCT function

COUNTIF function

Download an example file

To download an example file with all the examples that we are covering here click on the following link:

Counting unique values SUMPRODUCT + COUNTIF

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+