Forum Discussion
Solved
Count of duplicates in a column
Good morning!
Is there a formula that can be used to give me a count of duplicate values in a column?
To be clear, I'm not trying to count known duplicate values that I can include in a COUNTIF formula, such as =COUNTIF(A:A),"Specific Text". I'm looking for Excel to simply count how many times it sees a duplicate of any kind. See attached.
If you want the number of unique entries:
=COUNTA(UNIQUE(D3:D12))
If you want the number of entries that occur more than once:
=SUM(--(COUNTIF(D3:D12,UNIQUE(D3:D12))>1))
Can anyone help with this issue I'm having please?
When I try to count the unique values in the array (A1:Q27) in the sheet, it's returning 405 (cell A29) and I've no idea why? There are only 105 non-blank values in the array, and 459 possible cells, so I don't know where 405 is coming from?!
This is because it is a two-dimensional range. Convert it to a single column first:
=COUNTA(UNIQUE(TOCOL(A1:Q27,1)))
The second argument 1 in TOCOL makes Excel ignore blank cells.
Thank you so much, I didn't realise unique would only run on a single column/row.
If you want the number of unique entries:
=COUNTA(UNIQUE(D3:D12))
If you want the number of entries that occur more than once:
=SUM(--(COUNTIF(D3:D12,UNIQUE(D3:D12))>1))
- Thank you for your help.
