Excel – Array Formula for Count of Unique Values

The following formula shows something I did not know for a long time – it counts unique values just like that! Before knowing the existence of this formula, people (including me) had to do a lot of workaround simply to show the unique values. The workaround included sorting the values and then checking wheter the previous value is the same as the new one in a new column. Then adding a number if it was not and finally counting those numbers. Anyway, with the new formula, this meaningless workaround is now useless.

Enough talking here is the formula:

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

As you can imagine, this is an array formula and thus it should be executed with CTRL+SHIFT+Enter.

The second thing you should care about is changing the area in green to the area which you would like to work.

So, finally here is how the formula works:

Unique

Beautiful, isn’t it 🙂

If you are wise enough to embed the formula in VBA, with this thing you can do magic!

How does it work? That is really a good question! It simply counts the way every number is repeated in the range (e.g. 1234 – twice).Thus with our example we get the following:

Unique2

These numbers are divided by 1 and summed together. The result is the count.

Not very simple to understand, but fast, beautiful & reliable!

About

A VBA Developer.