Count Text

[Tech56]

Hi,

I have names in column A and would like to count the number of times "Bob" occurs but not "Bobby" is this possible?



Thanks

 

[AliGW]

Yes. However, there's not enough information: will there be other text in the cells or just the names?

 

[Tech56]

There will be names only but in different formats.

Bob, Jones
Jones, Bob
Bobby Jones
Jones, A. Bob

If I need to I can attach a file

Thank you

 

[bosco_yip]

1] Criteria "Bob" in C2

2] In D2, formula :

=SUMPRODUCT(0+ISNUMBER(SEARCH(" "&C2&","," "&A2:A5&",")))



Regards

 

[Juniad]

1] Criteria "Bob" in C2

2] In D2, formula :

=SUMPRODUCT(0+ISNUMBER(SEARCH(" "&C2&","," "&A2:A5&",")))

View attachment 65245

Regards

It is not counting the Bob if name something like this Bobby Bob Khan

 

[bosco_yip]

It is not counting the Bob if name something like this Bobby Bob Khan

Then, adding a SUBSTITUTE function and formula become :

=SUMPRODUCT(0+ISNUMBER(SEARCH(" "&C2&" "," "&SUBSTITUTE(A2:A6,",",)&" ")))



Regards

 

[Tech56]

Thank you very much Bosco that is perfect.