Given a column of names I would like a formula to return a count of unique names that appear >=X number of times. The function COUNTUNIQUE works only if X=1.


IF COUNT is more than 2,FILTER them in.

  • Exactly what I was looking for, thank you. I had previously seen FILTER, but misunderstood it's function. Just for clarity in case others find this, the final formula is: =COUNTUNIQUE(FILTER(A:A,COUNTIF(A:A,A:A)>=2)) Replace "2" with the number of times the value needs to appear. – Drexl Jul 2 '18 at 7:23

Here a solution to you problem. I am using a new column next to the column with the names to get a ranking.

enter image description here

formula to paste in B2 and drag drop to B11 =COUNTIF($A$2:$A2;A2)

formula to paste in B16 =SUMPRODUCT( ( ($B$2:$B$11)=B15 ) )

  • I have found similar solutions. This is good, but I was hoping to avoid adding new data like that. The holy grail would be solving this with a single formula in a single cell. – Drexl Jun 29 '18 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.