• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

excel 2010 count number of unique values in column

I have a column that has 98 unique values that occur 2000 times in said column.
i need to know how many times each unique value occurs.
2 Solutions
how are you going to use this? (in code, in a formula, exported to a file/database, etc.)
Saqib Husain, SyedEngineerCommented:

will give the number of times the value in B1 can be found in column A
Are you looking for a programmatic way to do this, or a formula to put into a cell?  If you're just looking to have the answer in a cell and know all the possible values (it'd take 98 cells to get the answer this way) you can simply use the formula


where A is the column and xxx is the value you want to count up.  That will populate the cell with the number of times xxx appears in column A.  Of course like I said, you'd need to create one cell for each of your 98 possible answers, and know what values you're looking for.  

If you don't know the values ahead of time, or need to access the results in vbscript for another reason, this won't do it.  It'd take me some time to rig up a script to loop through and count them all, so without knowing more details on your situation, this is the quick response.
Data->Choose 'Advanced' next to 'Filter'
'Copy to Another Location', ensure the list range is correct.
For 'Copy to' choose an empty column.  Check the 'Unique records only' box.

In the column adjacent to your newly created list of unique values: =COUNTIF(<originalListRange>,<uniqueValueCellReference>)

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now