Excel 2010 show how many times each text appears

**umen** · 01-23-2013, 06:24 AM

how do i count how many times each text appears in a column? for example say i have this column:

foo1
foo1
foo1
too2
foo1
too2
mmm
mmm
foo1

now i like to execute function that show me the result:

foo1 = 4
mmm= 2
too2 =2

can it be done in single advanced function ?

**Pepe Le Mokko** · 01-23-2013, 06:28 AM

Try the COUNTIF function

**umen** · 01-23-2013, 07:25 AM

sure i did , but then i need to do on each text instance

PHP Code:


=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

**Pepe Le Mokko** · 01-23-2013, 08:18 AM

First extract distinct values like this

Make sure your data has a header
Data --> Filter --> Advanced Filter
Check Unique Records Only
Select Copy to another location
Click OK
Then apply COUNTIF to each value in the list

**umen** · 01-23-2013, 08:29 AM

Thanks for the replay , how do i add headers ?
i do not have them

**Pepe Le Mokko** · 01-23-2013, 08:33 AM

Insert a cell at the top of the column with your data and name it what you want. From now on you have a header

**umen** · 01-23-2013, 09:09 AM

Originally Posted by Pepe Le Mokko

First extract distinct values like this

Make sure your data has a header
Data --> Filter --> Advanced Filter
Check Unique Records Only
Select Copy to another location
Click OK
Then apply COUNTIF to each value in the list

is there away to set automatically COUNTIF for each unique string?
i will have more then 5000 unique values ....

**ejla** · 01-23-2013, 03:52 PM

I use a pivot table with a filed option set to count, and than copy the results in the new sheet

**Pepe Le Mokko** · 01-23-2013, 03:57 PM

Originally Posted by umen

is there away to set automatically COUNTIF for each unique string?
i will have more then 5000 unique values ....

After using the filter ( say in col A), in B2 enter =countif(A:A,A2) then double click the handle at the right lower corner of this cell when selected