Count no. of occurrences of words or phrases in columns even if there are multiple phrases or words in one cell

[jeremypyle]

Hi,

I want to count the number of occurrences of words or phrases in columns even if there are multiple phrases or words in one cell.

Suppose I have the following:
a1=red cow
a2=blue cow
a3=red cow
a4=yellow cow, red cow
a5=blue cow
a6=red cow


Now I want a formula that will result in the following:
b1=red cow4
b2=blue cow2
b3=yellow cow1
b4=blue cow1

I will have several hundred thousand items in column A so I need a formula that I can simply drag down in column B that will show the number of the occurence of each phrase.

Is it also possible to make a formula that will do something similar except over multiple columns?

For example:
a1=red cow
a2=blue cow
a3=red cow
a4=yellow cow, red cow
a5=blue cow
a6=red cow

b1=black cow
b2=blue cow
b3=red cow
b4=pink cow, red cow
b5=orange cow

c1=pink cow
c2=milking cow
c3=purple cow
c4=black cow
c5=blue cow, red cow
c6=red cow
c7=white cow

Then in d1, I need the formula that will result in the following:
d1=red cow8
d2=blue cow4
d3=yellow cow1
d4=black cow2
d5=pink cow2
d6=orange cow1
d7=milking cow1
d8=purple cow1
d9=white cow1

I'm stuck so would greatly appreciate anybody's help on this. Thanks so much

 

[Piktro]

Are they always separated by commas?

 

[s hal]

I can only come up with a formula to find it if it occurs once in the cell in the whole range (if you are doing this for 200k+ cells, you are going to have a long calculation time, so be warned):

=COUNT(SEARCH("RED COW","$A1:$A200000,1))
entered with CTRL-SHIFT-ENTER
this would find how many times red cow appeared in the range A1:A200000, for instance (you could do this to A1:Z200000 and get the same results - to answer the multi-column part. I could probably fashion a solution for if the cell had it more than once, but its not coming to me off the top of my head...

 

[Piktro]

Data	Value in Cell	Total Entries
(A1) red cow	(B1) Yellow Cow	=COUNTIF(A$1:A$10, "*"&B1&"*")
yellow cow, red cow	Red Cow
red cow	Blue Cow
milk cow
blue cow
red cow
red cow
milk cow
blue cow
red cow

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Drag code in column C down as necessary. This would work for multiple columns as well, just change A$10 to the end of your data.

The "*"&B1&"*" code will put a wildcard before and after whatever is B1.
So, if B1 is Yellow Cow, and anywhere in the data it said 8233yellow cow23432, it would find it and count it.

Another way to separate out the commas in the data, which I would highly recommend, is using the Text-to-Columns tool under the Data tab, with a comma as the delimiter.

 

[jeremypyle]

ADVERTISEMENT

Are they always separated by commas?

Yes they are

 

[s hal]

I like Piktro's answer as its a little less intensive than mine.. That said, I also determined how to count the first AND second occurence of the variable in a cell:

=COUNT(SEARCH("RED COW",A1:A200000,1))+COUNT(SEARCH("RED COW",A1:A200000,LEN("RED COW")+SEARCH("RED COW",A1:A200000,1)))

You could add successive loops of the search + search + len to count the next Nth occurrence as well!
Yes, I had to one up you this time

 

[jeremypyle]

Thanks Piktro and S Hal. That helps so much!!! So grateful for your help =)