Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 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.

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.