Count occurrences of certain words based on another cell

[Drew_]

Hi all.

I have a column that will populate with text whenever entries are made. I want to count how many times certain words appear after other, separate words. For example, whenever a cell says "Bogey", I want to count how many time the very next cell says "Birdie" or "Eagle". Right now, I have a formula that works great:

=COUNTIFS(L7:L25,"*Bogey",L8:L26,"Birdie")/COUNTIF(L7:L25,"*Bogey")

However, this only counts the number of "Birdie" occurrences, and not "Eagle". I have a feeling I can accomplish what I want with the SUMPRODUCT or OR formulas but I don't know. Any help would be appreciated, thanks

 

[jarjarbingie]

Hi all.

I have a column that will populate with text whenever entries are made. I want to count how many times certain words appear after other, separate words. For example, whenever a cell says "Bogey", I want to count how many time the very next cell says "Birdie" or "Eagle". Right now, I have a formula that works great:

=COUNTIFS(L7:L25,"*Bogey",L8:L26,"Birdie")/COUNTIF(L7:L25,"*Bogey")

However, this only counts the number of "Birdie" occurrences, and not "Eagle". I have a feeling I can accomplish what I want with the SUMPRODUCT or OR formulas but I don't know. Any help would be appreciated, thanks

=(COUNTIFS(L7:L25,"*Bogey",L8:L26,"Birdie")+COUNTIFS(L7:L25,"*Bogey",L8:L26,"Eagle"))/COUNTIF(L7:L25,"*Bogey")