Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

After having searched Google & Co. for hours and trying various examples, I still could not figure out out how to solve what I thought would be trivial to calculate in Excel:

Having a table with e.g.

Column 1
--------
   A
   A
   B
   A
   C
   D
   D
   E

I want to know how often the distinct values are present. E.g. on a second sheet something like:

Column 1  |  Count
----------+-------
   A      |    3
   B      |    1
   C      |    1
   D      |    2
   E      |    1

My question is:

Is this possible at all to do with built-in Excel formulas?

If not, I would import it into an Access database and use some SQL to achieve the results, but I really would love to do it in Excel only.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Several ways to do this.

Pivot table

Insert a header row above the data and enter a label, for example "data". Make the label bold. Excel will more easily distinguish between the label and the data if the formatting is different. Select a cell in the column and click Insert > Pivot Table. Drag the "data" field into the rows panel and then drag the "data" field into the Values panel. It will default to counting the instances of the data. Less than 5 clicks and no formula.

Formula

Create a list of unique values that are present in your data. You can use a pivot table to create that list and copy and paste the unique values somewhere else. Then use a formula along the lines of

=COUNTIF(A:A,C10)

In this example, the list of unique values starts in cell C10. Copy the formula down.

The screenshot illustrates both approaches.

enter image description here

share|improve this answer
    
Phantastic, thanks a lot! –  Uwe Keim Jul 26 '13 at 7:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.