Count how many times a text appears in Excel 2010

Question

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

Answer 1

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.

Answer 2

This will do the trick with VBA

Sub mycount()
Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, 2), Unique:=True    
lastcell = Columns(2).Find(What:="*", After:=Cells(1, 2), SearchDirection:=xlPrevious).Row    
For Each mycell In Range(Cells(2, 2), Cells(lastcell, 2))
    mycell.Value = mycell.Value & " = " & WorksheetFunction.CountIf(Columns(1), Cells(mycell.Row, 2))
Next    
End Sub

Assuming all data is Column A and the rest of the sheet is empty
Important: You need to insert a header column for the unique filter to work

• open your workbook and press ALT+F11
• insert the code below into sheet1 or where ever your data is

• close the VBA editor and press ALT+F8 and ececute the macro

  

  _{Btw. your example has 5 x foo1}