array formulas counting substrings across rows
 

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.

array formulas counting substrings across rows
 

Assume your data in A1:A6
criteria start in A10

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


"Christi" wrote:

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.

array formulas counting substrings across rows
 

If C1 = Legal

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

If this post helps click Yes
---------------
Jacob Skaria


"Christi" wrote:

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.

array formulas counting substrings across rows
 

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

"Teethless mama" wrote:

Assume your data in A1:A6
criteria start in A10

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


"Christi" wrote:

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.

array formulas counting substrings across rows
 

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

"Jacob Skaria" wrote:

If C1 = Legal

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

If this post helps click Yes
---------------
Jacob Skaria


"Christi" wrote:

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.

array formulas counting substrings across rows
 

You're Welcome!

"Christi" wrote:

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

"Teethless mama" wrote:

Assume your data in A1:A6
criteria start in A10

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


"Christi" wrote:

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.

array formulas counting substrings across rows
 

Cheers..

If this post helps click Yes
---------------
Jacob Skaria


"Christi" wrote:

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

"Jacob Skaria" wrote:

If C1 = Legal

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

If this post helps click Yes
---------------
Jacob Skaria


"Christi" wrote:

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.