Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and Wildcards | Excel Discussion (Misc queries) | |||
Sumproduct Wildcards | Excel Worksheet Functions | |||
Wildcards in SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct with wildcards | Excel Worksheet Functions | |||
Wildcards with SumProduct | Excel Worksheet Functions |