Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) )))

--
isabelle



Le 2012-04-09 17:27, jt a écrit :
Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

On Mon, 9 Apr 2012 14:27:36 -0700 (PDT), jt wrote:

Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

If you want a count of unique duplicates, in other words, if the sequence:

1
2
2
2
2
1
1

should return 2 (duplicate numbers = 1 and 2), then try:

=SUM(--(FREQUENCY(A:A,A:A)1))

If this sequence should return 5 (1 is duplicated twice; 2 is duplicated thrice), then use Isabelle's formula

On Apr 9, 6:59*pm, isabelle wrote:
hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) *)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :



Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....

On Apr 9, 9:20*pm, john taiariol wrote:
On Apr 9, 6:59*pm, isabelle wrote:





hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) **)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :

Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....

what if the numbers are actually text??

On Mon, 9 Apr 2012 18:20:37 -0700 (PDT), john taiariol wrote:

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....


=SUM(--(FREQUENCY(A:A,A:A)0))

hi jt,

the first part (=COUNTA(A1:A100)) is all entries
the second part of the formula is the unique entries

=COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) )))

subtract the second part from the first gives the number of duplicates

--
isabelle



Le 2012-04-09 21:20, john taiariol a écrit :
On Apr 9, 6:59 pm, wrote:
hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) *)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :



Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....