SUMPRODUCT & Wildcards

[UWSPITHD]

I am using the forumla:
=SUMPRODUCT(N($E$2:$E$3=A11),N($J$2:$J$3="Very Satisfied"))
to match names in a table with the overall satisfaction they recieve. The
issue that I am running into is that some cells have more than one name in
them. With the formula above A11 = "Aaron" in E2 there are 4 names one of
which is Aaron. I need the formula to count his name even though it is not
the only thing in the cell. I have tried...
=SUMPRODUCT(N($E$2:$E$3=*A11*),N($J$2:$J$3="Very Satisfied"))
but it does not work. Any suggestions would be great.

 

[Bernie Deitrick]

=SUMPRODUCT(N(FIND(A11,$E$2:$E$3)>0),N($J$2:$J$3="Very Satisfied"))

HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

=SUMPRODUCT(N(ISNUMBER(SEARCH("Aaron",E2:E3))),N(J2:J3="Very Satisfied"))

 

[UWSPITHD]

That did it thank you.
I did change "Aaron" back to A11 as the names contantly change. Thank you
again.

 

[Teethless mama]

You're Welcome!

That did it thank you.
I did change "Aaron" back to A11 as the names contantly change. Thank you
again.