Hi,

Thank you in advance for reading this and for any help.

I would like to count the number of times a phrase appears in a cell when another cell within the corresponding row contains a specific value.
For example, below is some data (it’s formatted as a table which is referenced as being ‘Table1’), I would like a formula that counts how many times the phrase “collecting comic books” appears in the same row as “Tim” (I want to know how many people called Tim collect comic books).

(Note the issue this pertains to in real life the two columns are not next to each other)


Col A Col B
Name Hobbies
Charlie Running, Collecting Comic Books, Football
Charlie Ice-Skating, Playing Guitar
James Ice-Skating, Football
Rachel Collecting Comic Books
Rachel Video Games, Football
Rachel Video Games, Astronomy
Max Football, Astronomy, Ice-Skating
Max Running, Collecting Comic Books, Playing Guitar
Ben Playing Guitar, Video Games
Tim Astronomy, Ice-Skating
Tim Collecting Comic Books, Ice-Skating
Tim Collecting Comic Books



I tried messing around with a formula that uses LEN but I’m kind of out of my depth with that one… I’ve tried to simplify things by using ‘text to columns’ as the values in the 'hobbies’ column are comma delimited. This way instead of having to look for a phrase within a cell I just need to find cells with a specific value.

I tried using COUNTIFS and made a formula which looked like this:
=COUNTIFS(Table1[Name],"Tim",Table1[[Hobbies_text to columns_first column]:[Hobbies_text to columns_last column]]," Collecting Comic Books")

Here there are only two sets of range/criteria. Range One is the names column and the criteria is “Tim”. Range Two is the columns generated from my performing ‘text to columns’ on the hobbies column and the criteria is “Collecting Comic Books”. This doesn’t appear to work (perhaps because my second range includes empty cells?) and generates the #VALUE error.

I would very much appreciate either a solution that can count phrases that appear in cells (no ‘text to columns’ required). Or a solution from someone was able to follow on what I’ve tried and failed to do with the text separated into columns.


Thank you!