Count cells based on a text phrase within a series of text phrases

[TheSecretaryJen]

I want to be able to count the cells in a table column based on a phrase that is contained in the cell, but the cell will also contain other phrases separated by commas. Let's say the cells have text like this:

COLUMN HEADER
Active Member
Active Member, Interested Party
Interested Party
Uninterested Party, Active Member
Inactive Member, Uninterested Party
Inactive Member, Interested Party
Interested Party
Inactive Member
Active Member

I want a formula that will count the number of cells that include the phrase "Active Member", no matter what else the cell might contain and no matter what order the phrases are in. So in this example, the formula should return 4.

Is this possible without a macro? I can do macros, but my boss can't, and this spreadsheet is for him.

 

[Eric W]

You can use wildcards with the COUNTIF function, like so:

=COUNTIF(A:A,"*active member*")

However, in this case it would also match every row with "inactive member" since "active member" is a substring of that. To handle that, you can do this:

=COUNTIF(A:A,"*active member*")-COUNTIF(A:A,"*inactive member*")


Hope this works for you!

 

[TheSecretaryJen]

Never mind; I found the solution on another site. Here's the link, if anyone had the same question:

https://exceljet.net/formula/count-cells-that-contain-specific-text

 

[TheSecretaryJen]

Thanks, Eric W! The site I found said the same thing. I appreciate it!