33

I have a list of items that looks like this:

A
B
C
A
A
B
D
E
A

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.

2

3 Answers 3

28

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". Pick a destination cell for the "Copy To" location. enter image description here
  4. In the cell adjacent to the new unique list, add the formula =countif like this:

    =COUNTIF(F$16:F$24,I16)

    (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

4
  • 1
    The COUNTIF has a COUNTIF(range;criteria) syntax - hence there should be a semicolon instead of a comma.
    – Kozuch
    Nov 13, 2015 at 22:49
  • 1
    @Kozuch - In Excel and Office the syntax is a comma. support.office.com/en-us/article/…
    – jdh
    Nov 18, 2015 at 15:32
  • 5
    Ok, this probably goes back to the locale (language) settings - in my country the decimal delimiter is comma instead of period, so the syntax in COUNIF may have been changed to semicolon instead of comma as with the default English locale. I can not think of any other explanation.
    – Kozuch
    Nov 24, 2015 at 9:39
  • 1
    @Kozuch - good point. I am using the US English locale, and it bears repeating that the locale may effect some Excel syntax.
    – jdh
    Nov 24, 2015 at 16:23
22

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.

9

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .