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.

I have a list of items that looks like this:


Now I want to count the number of occurrences of each item. The result should look like this:

A 4
B 2
C 1
D 1
E 1

How can I do that? It is important to note that this should be flexible. That means if I add item F to the list, that item should also be considered in the result.

share|improve this question
Duplicate question: superuser.com/q/329128/76571 –  Excellll Jun 30 '12 at 16:08

3 Answers 3

Here's one way:

Assumptions: You want to keep the existing column/list untouched, and that you want this summary elsewhere:

  1. The next operation apparently needs a column header. Add a column header in the cell above your list.
  2. From Excel's Data tab, under Filter, pick the "Advanced" menu option (from the ribbon in Excel 2007/10)
  3. Select the range, including the new header. Select "Copy to another location" and check "Unique records only".
    enter image description here
  4. In the cell adjacent to the new unique list, add the formula =countif like this:


    (where the first parameter is the absolute range of the original list, and the second parameter is the relative cell of the unique value)

  5. Copy and paste this formula to the right of all unique cells.

  6. If auto calculate is turned off, hit F9 to update.
    The result is like this:

    enter image description here

share|improve this answer

Use a pivot table:

  • Add a header to your Item list (e.g., "Item" in cell A1)
  • Select column 1 (the whole column, not just the data that is there)
  • Insert pivot table
  • Drag "Item" to the row area, and also drag it to the value area
  • the value calculation should default to "Count"

If you add items to your list, simply refresh the pivot table to update the counts and/or pick up any new items.

share|improve this answer

Here you have a nice GIF showing how to in Excel. This is Mac OS X version, but it should not differe a lot.

enter image description here

share|improve this answer

Your Answer


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.