Newbie needs helping counting & tallying

[RichardK]

Hello,

Just started a new job and having a simple problem that I'd love it if one of you could help me fix.

I want excel to count the number of cells in a column that contain the same word and then tell me the number of times that word appears in the column, and do this for every word. Basically I want a quick way of finding out how many times each word or phrase appears in a large column of data.

For example, spreadsheet would say:

Blue Widget
Blue Widget
Blue Widget
Blue Widget
Red Widget
Red Widget
Green Widget
Green Widget
Green Widget

I want to run a function that will summarise the data to this:

Blue Widget: 3
Red Widget: 2
Green Widget: 3

I'm convinced there is a simple way to do this but haven't for the life of me been able to figure it out. Sorry if it's basic, but your help would really be appreciated!

[ajpowers]

=COUNTIF(BD12:BD17,"mary")

Type your specific range and that should take care of it.

[RichardK]

Cheers!

However, although it works I wanted something that would produce a frequency table with the number of occurences for each word/phrase. Do you know if this is possible?

Ta

[ajpowers]

Perhaps you didn't make the cell ranges in the formula absolute by typing "$" signs in front of the row and column? This is working for me.

=COUNTIF($A$1:$A$9,"Green Widget")
=COUNTIF($A$1:$A$9,"Red Widget")
=COUNTIF($A$1:$A$9,"Blue Widget")

Results are:

3
2
4

[Ron Coderre]

Try a Pivot Table:

Select the list, then
Data>Pivot Table
Use Excel List
Select where you want the Pivot Table
then Click Layout
Row: Product
Data: Count of Product

Does that get you what you want

Ron

[RichardK]

Thanks to both of you, but Ron gets the points!

That's exactly what I was looking for - going to have to learn more about these pivot tables!

Cheers guys