Excel – Count how many times a text appears in Excel 2010

microsoft-excel-2010worksheet-function

How do I count how many times each text appears in a column?
For example I have this column:

foo1
foo1
foo1
too2
foo1
too2
mmm
mmm
foo1

Now I like to excute a function that shows me this result:

foo1 = 4
mmm= 2
too2 =2

Can this be done in a single advanced function?
i did try with COUNTIF but then i need to do on each text instance. 

=COUNTIF(A1:A22601,"foo1")

but then i need to know which text's i have in each column.
what i want is function that will take the column and calculate each text how many instances it has in the column

Best Answer

If you add a header row with a label to your data, you can build a pivot table

  • click any cell in the column
  • click Insert ribbon > Pivot Table
  • In the field pane drag the field rom the top panel into the row box and then drag it into the value box

If you don't want a pivot table, you can create a list of unique values by selecting the data. Then click Data ribbon > Advanced (in the Sort & Filter group). Tick the box for "Unique records only". Also tick "Copy to another location" and in the "copy to" box specify a cell. This will give you a list of unique text values. Let's say this list starts in cell G3. In cell H3 you can then use a Countif like

=COUNTIF(A:A,G3)

and copy that down.

Related Question