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.
--
Michael

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael" wrote in message
...
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.
--
Michael

Try this:

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

Hope this helps.

Pete

On Aug 18, 10:53*pm, Michael
wrote:
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.
--
Michael

Thanks. That worked great.
--
Michael


"Pete_UK" wrote:

Try this:

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

Hope this helps.

Pete

On Aug 18, 10:53 pm, Michael
wrote:
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.
--
Michael