SOLVED

Count of duplicates in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-2994039%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20duplicates%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2994039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1141560%22%20target%3D%22_blank%22%3E%40Danger_SF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(1%2FCOUNTIF(D3%3AD12%2CD3%3AD12))%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20Enter%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2993912%22%20slang%3D%22en-US%22%3ECount%20of%20duplicates%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2993912%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20morning!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20that%20can%20be%20used%20to%20give%20me%20a%20count%20of%20duplicate%20values%20in%20a%20column%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20be%20clear%2C%20I'm%20not%20trying%20to%20count%20known%20duplicate%20values%20that%20I%20can%20include%20in%20a%20COUNTIF%20formula%2C%20such%20as%20%3DCOUNTIF(A%3AA)%2C%22Specific%20Text%22.%20I'm%20looking%20for%20Excel%20to%20simply%20count%20how%20many%20times%20it%20sees%20a%20duplicate%20of%20any%20kind.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2993912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

 

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.

2 Replies
best response confirmed by Danger_SF (Occasional Contributor)
Solution

@Danger_SF 

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.