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))
Share
Related Posts

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

4 Responses to "Count Duplicate Values in a range"
1. Very helpful formula. Thank you.

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.

3. hi.. i have data where i have duplicate records on the basis of Code,name,and date column and i have to set a status for expiry date so if i have duplicate record on the basis of Code+name then i will check date.
If both the dates are same then date will come in expiry date
If one date is blank and another date is available then blank status will appear stating as No expiry
if both dates are different then maximum date will appear

4. Code Name EndDate ExpiryDate
1 Deepa 31/07/2020 Expired or expiring within 6 months
1 Anu 31/08/2022 Expiry 2-3 years
2 Trishika 30/04/2020 No Expiry Date
2 Trishika No Expiry Date
3 abc 31/08/2024 Expiry 5 years or more
3 abc 01/09/2022 Expiry 5 years or more
4 asd No Expiry Date
4 asd No Expiry Date

Next → ← Prev

Looks like you are using an ad blocker!

To continue reading you need to turnoff adblocker and refresh the page. We rely on advertising to help fund our site. Please whitelist us if you enjoy our content.