Count duplicate values in a list

[Swaroon]

Hi,

Is there any formula that will cover the following -

Column A
1
2
1
2
3
2
3
3

The required answer is 3, ie there are 3 values in the list, although they have been repeated, I need a count of how many numbers have been used.

thanks for your help
Steve

 

[Swaroon]

I found it

=Countdiff

thanks

Steve

 

[Andrew Poulsom]

Try:

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

which must be confirmed with Ctrl+Shift+Enter not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.

 

[Swaroon]

Hi,
Thanks, I tried it but it didn't work, all I got was a #DIV/0!
I used Ctrl+Shift+Enter but nothing happened.

No probs as I've got the Countdiff to work.

thanks
Steve