Count number of UNIQUE values in Excel

Count number of UNIQUE values in Excel

Problem: I have the following data range with duplicate values in column C and I want to retrieve the number of unique entries to cell J2. What can I do ?

Well, you could obviously do it manually by using the Remove Duplicates function or a Pivot Table. The setback is that if you add new entries to the original data range you'd have to run the Remove Duplicates function or create/update the a Pivot Table again.

Solution: Add the following excel formula into cell J2 and it will automatically count the number of unique entries on range C2:C11.

=SUM(1/COUNTIF(C2:C11);C2:C11))

Press CTRL + SHIFT + ENTER.

The result formula in cell J2 will be:

{=SUM(1/COUNTIF(C2:C11);C2:C11))}


Note: The curly braces {} indicate that this is an array formula. Do no type these yourself. They will disappear when/if you edit your formula.


Explanation of the formula Step-by-Step:
The COUNTIF function will create an array, stored in Excel's memory, that looks as follows:

{3;1;2;1;1;3;1;2;1;3} - (three Jones's, one Kivell, two Jardine, one Gill and so on...)

This reduces to:
{1/3;1/1;1/2;1/1;1/1;1/3;1/1;1/2;1/1;1/3}

This array constant is used as an argument for the SUM function, giving a result of
1/3+1/1+1/2+1/1+1/1+1/3+1/1+1/2+1/1+1/3 = 7 (Jones; Kivell; Jardine; Gill; Sorvino; Andrews; Thompson)