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
Assuming data is entered in range A2:A10.
=SUMPRODUCT(--(COUNTIF(A2:A10,A2:A10)>1))
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 worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Very helpful formula. Thank you.
ReplyDeleteI 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.
ReplyDeletehi.. 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.
ReplyDeleteIf 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
Code Name EndDate ExpiryDate
ReplyDelete1 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