Calculating Repeating Numbers

[Surrey Seagulls]

I am hoping to calculate the number of times a number repeats.

i.e. in Column A1 to A10 the numbers are as follows;-

435,675,873,435,900,675,873,212,435,900

The answer I am looking to generate is 5 as the number of different numbers is 5.

I am at a loss, so if any one could help I would be much appreciated.

Thanks

Steve

 

[Aladin Akyurek]

On 2002-08-28 07:19, Surrey Seagulls wrote:
I am hoping to calculate the number of times a number repeats.

i.e. in Column A1 to A10 the numbers are as follows;-

435,675,873,435,900,675,873,212,435,900

The answer I am looking to generate is 5 as the number of different numbers is 5.

I am at a loss, so if any one could help I would be much appreciated.

Thanks

Steve

You mean the number of distinct numbers in A1:A10...


Array-enter:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

To array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

If you download and install the morefunc add-in, you can just use:

=COUNTDIFF(A1:A10)

 

[Surrey Seagulls]

Thanks Aladin Akyurek it works a treat

Regards

Steve