Most Common Words in a Column

**bbarth** · 04-07-2015, 10:50 AM

I need to know how many times a word comes up a column. For example in column "A" I have listed

Apples Pears
Peaches Apples
Apples
Oranges
Peaches

So then what I need is

Column B Column C
Apples 3
Pears 1
Peaches 2
Oranges 1

The order doesn't matter so much as knowing how many times each word was listed in the column.

Thank you for your help!

**AlKey** · 04-07-2015, 11:03 AM

Try this one

in C1 and copy down

=COUNTIF($A$1:$A$5,"*"&B1&"*")

Row\Col	A	B	C
1	Apples Pears	Apples	3
2	Peaches Apples	Pears	1
3	Apples	Peaches	2
4	Oranges	Oranges	1

**bbarth** · 04-07-2015, 11:14 AM

Thank you for your help. I tried this and it gives me a warning that there is a circular reference and if I click ok then I just get 0's in column B. The only way I can get this to work is if I put it in column "C" and type in "Apples" in column "B". However, I need the formula to also list the word in column "B" as it will not be feasible for me to do this by hand for my document. Thank you again for your help.

**AlKey** · 04-07-2015, 11:18 AM

Sorry, it was my mistake it should be C1

It is very difficult sometimes to understand what is what and where is it should be.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**bbarth** · 04-07-2015, 11:58 AM

Here is a sample of what I am needing. Thank you!

**AlKey** · 04-07-2015, 12:08 PM

This is the same layout that I had in my first post to you.

**bbarth** · 04-07-2015, 12:16 PM

I see how this works, but unless I am missing something I have to fill in column "B" for it calculate column "C". However, I need it to calculate both columns "B" and "C" from the data in column "A". Thank you for your help!