Help with a sum formula

[Michael]

I am having trouble getting the right number from a formula and I was
wondering if anyone can help me correct my error.

In column B I have a mix of names that I am only trying to pick one out of
such as

B5 contains Joe, Paul, John, etc

And column E contains Yes or No

I need to count how many times a name appears with a yes but having multiple
names in the cell is throwing it off. The formula I am trying to use is-

=SUM((B5:B320="*Paul*")*(E5:E320="Yes"))

and then hitting ctrl, shift, enter to put it in array
It is returning a 0, which is incorrect. If I try the formula with only one
name in a box and get rid of the ** it works fine. Any Ideas? Any help is
appreciated. Thanks.

 

[Don Guillett]

Try
=SUMproduct((B5:B320="Paul")*(E5:E320="Yes"))

 

[Pete_UK]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("Paul",B5:B320)))*(E5:E320="Yes"))

Hope this helps.

Pete