1

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.

Uwe Keim
  • 2,112

1 Answers1

4

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

teylyn
  • 23,615