Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
    
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
1  
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.

share|improve this answer
    
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

You must log in to answer this question.

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