How many instances of a column with duplicated values

[Masha92]

Hello all,

I have been struggling with this one and couldnt find answers on google! Though am sure the answer is fairly easy.

So i have a list of duplicate values in a column as below.

A
A
A
B
B
C

I need an excel formula (non vba) which results "3". Because we have only A, B and C regardless of how many times it exists.

Can you please help me with this one?

Thank you!
Masha

 

[Saba Sabaratnam]

Hi Masha,

I have your data in Column A as shown below.

Enter the following formula in B2



=SUMPRODUCT(1/COUNTIFS(A1:A6,A1:A6))

Kind regards

Saba

 

[Masha92]

Thank you So much Saba! This works )