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.

Name	Type
Fred	Text123
Fred	Data
Fred	Data
John	Text231
John	Data
John	Text254
Carl	Text123
Carl	Data
Carl	Text231
Carl	Data

<tbody>
</tbody>

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!