excel 2010 count number of unique values in column

Posted on 2012-08-13
Last Modified: 2012-08-13
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.
Question by:cmkeur
    LVL 44

    Expert Comment

    how are you going to use this? (in code, in a formula, exported to a file/database, etc.)
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed

    will give the number of times the value in B1 can be found in column A
    LVL 9

    Assisted Solution

    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.
    LVL 4

    Accepted Solution

    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>)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now