Count Duplicate Values in a range

Suppose you want to count cells that have duplicate values.

Assuming data is entered in range A2:A10.

Paste the following formula in cell B2
=SUMPRODUCT(--(COUNTIF(A2:A10,A2:A10)>1))

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
2 Responses to "Count Duplicate Values in a range"
  1. Very helpful formula. Thank you.

    ReplyDelete
  2. I want to count COUNT DUPLICATE VALUES IN A RANGE but with another criteria (Y) represented by numerical value 0-8. If Y>4 then the rank value of the data will be send backward in the sequence.

    ReplyDelete

Next → ← Prev