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))
Related Posts
About Author:

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

4 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
  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

    ReplyDelete
  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

    ReplyDelete

Next → ← Prev
Love this Post? Spread the Word!
Share