• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • 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>)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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