Using Excel: How to count unique values

Recently I had to display the number of unique people who were in a list. Interestngly there isn't builtin function in Excel that'll give you this information, which is kind of nuts of you think about. But the good news is, it's not difficult to accomplish. Hint: Google is your friend here :-)

There are a couple of different ways of doing this. One is general purpose and will work with any data but it can be slow. The other is very elegant and much faster but only works with numeric data. Very useful for staff numbers etc. So lets start with the elegant method.

Elegant formula for numeric values

=SUM(--(FREQUENCY(data_range,data_range)>0))

e.g.
=SUM(--(FREQUENCY(Table1[Staff_Nbr],Table1[Staff_Nbr])>0))

How it works

FREQUENCY returns an array of numbers with the frequency that values have occurred in, in the location where the number it's counting, is. But FREQUENCY has a special property that it returns 0 if it's already seen that particular number before. Hence you only get one positive number per unique values it sees. This is the bit we're exploiting. Now we need to turn any number greater than 1 into a 1 so we can SUM it later. So we add a logic test, that's the ">0" bit, that'll make everything into TRUE or FALSE. Transforming the TRUE/FALSE values back in to 1's and 0's is what the "--" function does. Finally we SUM the list. That gives us a count of all the unique numeric values in the list by a very elegant, if somewhat complex, manner.

Based on a post in Exceljet - Count unique numeric values in a range.

Generic formula for any value

This formula has the advantage that'll it'll work on any data not just numeric data. It uses COUNTIF, but if you look at the formulas you'll realise it has the potential to be quite slow on larger spreadsheets.

=SUMPRODUCT(1/COUNTIF(data,data))

How it works

COUNTIF generates an array of values with the number of times it's found each value as the elements in the array. If it finds a value once, it'll return a 1 in that location. If it finds a number three times it'll return a 3 in each of the locations it found the number. OK, now for the magic bit, you divide 1 by each of the numbers in the array to give you another array. So if a number appears once it'll be 1/1=1. If a number appears 4 times it'll be 1/4 = 0.25, but that value will appear 4 times, once for each of the locations it was found. 4 x 0.25 = 1. If you then total that array with SUMPRODUCT, you'll end up with the total number equal to the number of unique values in the data. Neat, huh? And it works for numbers and text. But like I said it can be quite slow for larger tables.

Based on a post in Exceljet - Count unique values in a range with COUNTIF.

Further Reading

If you do want to investigate these formulas further I highly recommend you have a look at Count unique values in a range with COUNTIF by Dave Burns of Exceljet. This post also has some more examples of how to do counts with criteria and how to count text values using FREQUENCY and MATCH.

Inspired by a post by Dave Bruns on Exceljet entitled Count unique values in a range with COUNTIF.

"Don't just count your years, make your years count." - George Meredith

Header image licensed from 123RF.com