# Calculating Repeating Numbers

#### Surrey Seagulls

##### New Member
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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)

Thanks Aladin Akyurek it works a treat

Regards

Steve

Replies
4
Views
349
Replies
7
Views
630
Replies
2
Views
186
Replies
5
Views
221
Replies
4
Views
547

1,221,241
Messages
6,158,733
Members
451,513
Latest member
EbenAgya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back