SOLVED

How do I count the occurrence of unique values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2639689%22%20slang%3D%22en-US%22%3EHow%20do%20I%20count%20the%20occurrence%20of%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639689%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20count%20the%20occurrence%20of%20unique%20values%20for%20over%201000cells%20in%20a%20column%3F%3C%2FP%3E%3CP%3EAfter%20getting%20the%20answers%2C%20is%20there%20a%20short%20cut%20for%20autocomplete%20without%20having%20to%20drag%3F%3C%2FP%3E%3CP%3EI've%20tried%20this%2C%20doesn't%20seem%20to%20work%20(%26nbsp%3B%20%3DCOUNTIF(%24B%242%3A%24B%243794%2CB2%26nbsp%3B%20%26nbsp%3B).%26nbsp%3B%20Help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2639689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639772%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20count%20the%20occurrence%20of%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639772%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126760%22%20target%3D%22_blank%22%3E%40david_odolofin%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EWhich%20version%20of%20Excel%2FOffice%20do%20you%20have%3F%20Excel%20in%20Microsoft%20365%20has%20a%20new%20function%20UNIQUE%20that%20will%20return%20a%20list%20of%20unique%20items%20in%20a%20range.%20COUNTA(UNIQUE(...))%20can%20be%20used%20to%20count%20unique%20items.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639803%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20count%20the%20occurrence%20of%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BOffice%20365(%20Excel%20version%3A%202006)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How do I count the occurrence of unique values for over 1000cells in a column?

After getting the answers, is there a short cut for autocomplete without having to drag?

I've tried this, doesn't seem to work (  =COUNTIF($B$2:$B$3794,B2   ).  Help.

4 Replies

@david_odolofin

Which version of Excel/Office do you have? Excel in Microsoft 365 has a new function UNIQUE that will return a list of unique items in a range. COUNTA(UNIQUE(...)) can be used to count unique items.

@Hans Vogelaar Office 365( Excel version: 2006)

@david_odolofin 

Try

 

=COUNTA(UNIQUE($B$2:$B$3794))

best response confirmed by david_odolofin (New Contributor)
Solution

@david_odolofin 

Or, if you want to count the number of occurrences of each value, create a pivot table based on your column. Add the column to both the Rows area and the Values area.