# Count how many times a text appears in Excel 2010

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

-
This seems to be a common question and was answered multiple times. Have a look at the `countif` statement – nixda Jan 23 '13 at 11:06
i updated the question – user71020 Jan 23 '13 at 11:28
What do you want your output to look like? `foo1 4`,`foo1 4`,`mmm 2`,`mmm 2`,`mmm 2`,`too2 2` etc? – Andi Mohr Jan 23 '13 at 11:28
Ahh... you need a Pivot table. Try this: office.microsoft.com/en-gb/excel-help/… – Andi Mohr Jan 23 '13 at 11:40

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.

-
how can i sort the value box ? i can only sort the row box – user71020 Jan 24 '13 at 6:30
click any cell in the column, then click Data > Sort – teylyn Jan 28 '13 at 1:23