array formulas counting substrings across rows

[Christi]

Data looks like:
Row Column A
1 Legal,Sales
2 Financial,Legal,Sales
3 Financial,Legal,Sales
4 Financial
5
6 Financial,PII,Business Development

The answer set should look like below. Column A will be prepopulated with
the values we're interested in counting. Column B should be the formulas

Row Column A Column B
1 Legal 3
2 Financial 4
3 PII 1
4 Business Development 1
5 Sales 3

I'm very familiar with array formulas, but can't crack this nut without
getting errors. Any help is greatly appreciated.

 

[Teethless mama]

Assume your data in A1:A6
criteria start in A10

In B10: =COUNTIF($A$1:$A$6,"*"&A10&"*")
copy down

 

[Jacob Skaria]

If C1 = Legal

=COUNTIF(A:A,"*" & C1 & "*")

If this post helps click Yes

 

[Christi]

I was trying to make this WAY harder than it was! Thanks so much!

 

[Christi]

I was trying to make this WAY harder than it was! Thanks so much!

 

[Teethless mama]

You're Welcome!

I was trying to make this WAY harder than it was! Thanks so much!

 

[Jacob Skaria]

Cheers..

If this post helps click Yes