Counting text only when specific text value appears in different column on same row.

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!

You need to put asterisks around the text you are looking for, like this:

"*Collecting Comic Books*"

The asterisks are wildcard characters, which mean any or no characters before and after the text, so it enables you to do "contains".

You are getting the #VALUE error as the ranges are not the same size - using the asterisks you will not need to do Text-to-Columns, so your formula would become:

=COUNTIFS(Table1[Name],"Tim",Table1[Hobbies],"*Collecting Comic Books*")

Hope this helps.

Pete