Count of values which appear more than once in a column

Question

In my excel column I have values as such:

ID
a
a
a
b
c
c
d
e

I would like to return the count of ids which occur twice or more. In this case answer is 2 (a,c).

Constraints:

1. No helper cols or one at most(There are a ton of other filters to be added to the countifs which are not relevant to the question,adding helpers would mean 12+ extra columns, one for each month)

2.No VBA ( UDF is ok)

3.Formula result in single cell.

The current formula which I have tried: =COUNTIFS(F13:F22,COUNTIF(F13:F22,">=2")) gives me 0.

Thanks in advance.

Answer 1

Hmm with no specific order of values, try:

=SUM(IF(COUNTIF(A2:A9,A2:A9)>1,1/COUNTIF(A2:A9,A2:A9),0))

Enter as array through CtrlShiftEnter

Another variant would be:

=SUMPRODUCT((COUNTIF(A2:A9,A2:A9)>1)/COUNTIF(A2:A9,A2:A9))

With the advantage you won't have to enter as array.

Would you choose to add criteria I believe that the second formula is a bit more userfriendly adding them in, like so (edited your sample data a little to show):

=SUMPRODUCT((B2:B9=1)*(C2:C9="x")*(COUNTIF(A2:A9,A2:A9)>1)/COUNTIF(A2:A9,A2:A9))