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:
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:
These numbers are divided by 1 and summed together. The result is the count.
Not very simple to understand, but fast, beautiful & reliable!