Dynamic COUNTIF function looking for text value in comma-separated string

Hi everyone,

I'm not very savvy with excel, so I think this should be an easy fix for one of you experts.

I have a database full of peoples' personal interests (i.e. Agriculture, Finance, Technology, etc.) and they are allowed to select as many interests as they want.

When importing this data, their interests show up in one cell, separated by commas. So if Person A chose that they were interested in Agriculture and Technology, it would show up in the cell as "Agriculture, Technology".

I have column headings for each of the particular fields and want to create a dynamic COUNTIF function that will search for how many times each interest shows up in a range of date. I'd like to be able to drag this function across my spreadsheet so it searches the entire range based on my different column headings.

I'm searching the G column in another sheet for "Agriculture" (which is in B32), but it's only returning the number of cells that equal EXACTLY "Agriculture". I want the search to count if the cell contains "Agriculture" anywhere within the comma-separated cell.

=COUNTIF('OAP talent pool'!G4:G2537,"=" & B32)

Thanks!

That's it, thanks a ton Tony!

Hey, what if I wanted to use thw word "Agriculture" and not cell B32 what will the formulae look like?

I have tried =COUNTIF('OAP talent pool'!G4:G2537,"*"&Agriculture&"*") but it doesn't work.

That worked, thank you.

Hi guys, i'm having some issues implementing the ideas on this thread to my work. I have 2 criteria, camera number and event type. I need to know how many different cameras have been used for each event type. The camera number is a text field and uses commas as it's very likely more than 1 camera being used. I trouble i am having is that I have cameras called S1 and also 1, so the * on the "*1*" formula is bringing in camera number 1 and S1. Similarly this will apply for cameras 1 and 10 etc etc. I've inserted a snapshot below. Any help would be warmly received.

Untitled.png