Count Multiple Unique Values with Wildcards

[Diamond_D]

I need help with a formula to count unique values in one column that match data in another column but using a wildcard. Column A has multiple names repeated in sequence and column B has data for every occurrence of a name. I want to be able to count the number of names that have Text in a cell in column B and only count once. So, in the example table Fred, John and Carl would all equal 1. Formula needs to count Text (with wildcard) but only once per person.

[TABLE="width: 257"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Text123[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Text231[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Text254[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]Text123[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]Text231[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

Hope you can help

 

[Eric W]

Welcome to the forum.

Try:

=SUM(SIGN(FREQUENCY(IF(ISNUMBER(SEARCH("text",B2:B11)),MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1)))

confirmed with Control+Shift+Enter when you enter it into the formula bar, not just Enter.

 

[Diamond_D]

Welcome to the forum.

Try:

=SUM(SIGN(FREQUENCY(IF(ISNUMBER(SEARCH("text",B2:B11)),MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1)))

confirmed with Control+Shift+Enter when you enter it into the formula bar, not just Enter.

Thanks Eric, worked a treat!

 

[Eric W]

Glad to help!