count duplicates

[jt]

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.

 

[isabelle]

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 :

 

[Ron Rosenfeld]

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

 

[john taiariol]

hi jt,

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

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

 

[john taiariol]

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??

 

[Ron Rosenfeld]

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))

 

[isabelle]

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 :