SUMPRODUCT & Wildcards
 

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.

SUMPRODUCT & Wildcards
 

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

HTH,
Bernie
MS Excel MVP


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

SUMPRODUCT & Wildcards
 

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


"UWSPITHD" wrote:

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.

SUMPRODUCT & Wildcards
 

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

"Teethless mama" wrote:

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


"UWSPITHD" wrote:

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.

SUMPRODUCT & Wildcards
 

You're Welcome!


"UWSPITHD" wrote:

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

"Teethless mama" wrote:

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


"UWSPITHD" wrote:

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.