Count of duplicate values

[tony0682]

I want to be able to see how many values in a dataset are repeated more than once. it doesn't matter what the value is. I just want to see how many values do not uniquely appear in a list.

For example, the following list of numbers each in its own row.

1
2
5
3
c
6
z
g
z
z
1
3
3
3

The result should tell me 3 because 1, 3, z have duplicate entries in the list. Is this possible?

 

[Mike LH]

Hi,

Try this.

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&"")-(COUNTIF(A1:A50,A1:A50&"")=1))

 

[tony0682]

If my dataset were then the following;

1 y
2 y
5 n
3 y
c n
6 n
z y
g y
z y
z y
1 n
3 y
3 y
3 y

How would I change the formula to look for the duplicates only if the 2nd column has a "y". This set should return 2.

 

[tony0682]

I want to count how many times a value shows only once, twice, three times,etc... How do I mod this formula to accomplish that? Is there a frequency formula I can use?