Calculating duplicates
 

Hi all,
How I calculating duplicates in excel?
Example:
A1: 1
A2: 2
A3: 2
A4: 3
A5: 3
A6: 3
A7: 4
Answer: How many duplicates: A8: 3
Answer: How many unique values: A9: 4

I meen, what kind of functions I need?
This is same function that tool "Remove Duplicates" but I need automatic
information in Excel cells.

Calculating duplicates
 

--In cell A8
=MAX(FREQUENCY(A1:A7,A1:A7))

--In cell A9
=SUMPRODUCT((A1:A7<"")/COUNTIF(A1:A7,A1:A7&""))

Incase your data do not have any blank entries you can try the below formula
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))


If this post helps click Yes
---------------
Jacob Skaria


"backmara" wrote:

Hi all,
How I calculating duplicates in excel?
Example:
A1: 1
A2: 2
A3: 2
A4: 3
A5: 3
A6: 3
A7: 4
Answer: How many duplicates: A8: 3
Answer: How many unique values: A9: 4

I meen, what kind of functions I need?
This is same function that tool "Remove Duplicates" but I need automatic
information in Excel cells.

Calculating duplicates
 

shouldn't be A8=2 ?

On 10 Lis, 09:01, backmara wrote:
Hi all,
How I calculating duplicates in excel?
Example:
A1: * * 1
A2: * * 2
A3: * * 2
A4: * * 3
A5: * * 3
A6: * * 3
A7: * * 4
Answer: How many duplicates: * * * A8: 3
Answer: How many unique values: A9: 4

I meen, what kind of functions I need?
This is same function that tool "Remove Duplicates" but I need automatic
information in Excel cells.