Another way to count unique values in a range in Excel

Not long ago I wrote an article about how to count the number of unique values in a range. That trick was basically using the SUMPRODUCT and COUNTIF functions. Today we are going to see another way to calculate it.

To understand what we are trying to do it is always best to use an example. We have a list of names in the range A1: A14, which we have called Name_list.

How many of these names are unique? The answer is obviously 11. But how we calculate it? We saw earlier that can be calculated using the SUMPRODUCT and COUNTIF functions, but today I want to show you another way to perform the same calculation, but this time using the FREQUENCY and MATCH functions.

And what formula is that?

The formula in question is:

{=SUM(IF(FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))>0,1,0))}

This formula is an array formula or matrix. Let's see how it works step by step.

To view the full results of each of the formulas it is necessary to press F2 + F9, once in the cell.

1. =MATCH(Name_list,Name_list,0)

To understand a formula of this length, the main thing is to start with the smaller side, which is usually more in the centre. In this case the first thing to understand is what makes MATCH(Name_list,Name_list,0). First checks MATCH if you have any doubt how the function works.

This array formula would be telling us for each of the names, in what position on the list they appear first.

=MATCH(Name_list,Name_list,0) = {1; 2; 3; 4; 5; 6; 1; 3; 9; 10; 11; 12; 13; 10}

As shown in the result only the duplicates have a different number compared to its position. It can be seen that Arthur, who is one of the names repeated as it is in A1 and A7, obtained in both cases the value 1, telling us that the first value in the list Arthur appears is in the first position.

2. =ROW(Name_list)

The ROW function is what produce a matrix with all numbers from 1 to 14, having 14 values ​​in our range.

=ROW(Name_list) = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}

3. =FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))

The frequency function calculates the frequency that a value occurs within a range of values ​​and returns a vertical array of numbers.

In our case, basically it counts the times each value appears in the list.

=FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list)) =

{2, 1, 2; 1; 1; 1; 0; 0; 1; 2; 1; 1; 1; 0; 0}

As can be seen in the matrix obtained in step 1, there are 2 values ​​equal to 1, 2 equal to 3 and 2 equal to 10, which obviously indicates that both Arthur, Ingrid and James have duplicates.

4. =IF(FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))>0,1,0)

The IF function will give a value of 1 to all names that are not duplicates, and duplicates (only them, not the original value) will be given a 0.

=IF(FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))>0,1,0) = {1; 1; 1; 1; 1; 1; 0; 0; 1; 1; 1; 1; 1; 0; 0}

5. =SUM(IF(FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))>0,1,0))

Finally add all values ​​of the resulting matrix,

=SUM(IF(FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))>0,1,0)) = 11

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 FREQUENCY + MATCH

Learn how to create awesome dashboards

Click here for dashboard examples.

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+