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 :

 

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

 

[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 :