Pulling two pieces of info from table into one cell

[ExcelGuy12345]

Hello All,

Is it possible to enter two pieces of text information into a cell and have it calculate an answer?

For example, I'm trying to make it so an employee can simply punch in a code word for a "job" and have it compile the total time in an adjacent cell. The problem is that I need the ability for him to enter two or more different words into the cell and have it spit out the total calculated time on the other end. I'm trying not to change too much on the sheet so I don't confuse things for him.

Currently, I have a SUMPRODUCT ----=SUMPRODUCT(SUMIF(K2:K4,B2,L2:L4)*C2)---- pointing to a coded table where "Job A" = 5 minutes, "Job B" = 10 minutes, etc. So, all he has to do is enter "Job A" into his cell and it returns a time. However, I need him to be able to enter "Job A/Job B/Job C" and have it add all the times together (in the mentioned formula, the cell would be "B2".)

Also, is there a way to make it so he doesn't have to type in the exact word to get the desired result? Sometimes, there is a typo which makes the key word unrecognizable so the formula doesn't work. I'd like to be able to make it so he doesn't have to type exactly "Job A/Job B" or whatever. I'd like that if he used a "," instead of a "/" or added an extra space somewhere, it wouldn't throw things off.

Thanks for the assistance, and if there's any more information needed, please let me know!

 

[Roderick_E]

Here it is, the magical formula

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),IF((LEN(SUBSTITUTE(B2,",","/"))-LEN(SUBSTITUTE(SUBSTITUTE(B2,",","/"),"/","")))/LEN("/")=1,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2))),L:L)))*C2,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2)-FIND("/",SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1))),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(reverse(LEFT(reverse(SUBSTITUTE(B2,",","/")),FIND("/",reverse(SUBSTITUTE(B2,",","/")))-1))),L:L)))*C2))

This handles:
1) converting , into / in case user input , as a delimiter instead of /
2) removes leading and trailing spaces from the job code input
3) 1, 2 or 3 job codes input into the cell

You still would need to add to your list any variations of a single job code such as A for Job A or JobA for Job A so maybe:
Job A
Job B
Job C
JobA
JobB
JobC
A
B
C

 

[ExcelGuy12345]

Here it is, the magical formula

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),IF((LEN(SUBSTITUTE(B2,",","/"))-LEN(SUBSTITUTE(SUBSTITUTE(B2,",","/"),"/","")))/LEN("/")=1,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2))),L:L)))*C2,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2)-FIND("/",SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1))),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(reverse(LEFT(reverse(SUBSTITUTE(B2,",","/")),FIND("/",reverse(SUBSTITUTE(B2,",","/")))-1))),L:L)))*C2))

This handles:
1) converting , into / in case user input , as a delimiter instead of /
2) removes leading and trailing spaces from the job code input
3) 1, 2 or 3 job codes input into the cell

You still would need to add to your list any variations of a single job code such as A for Job A or JobA for Job A so maybe:
Job A
Job B
Job C
JobA
JobB
JobC
A
B
C

Thank you, Sir. For some reason the updated formula is kicking back an incorrect result. It seems to only be grabbing one of the job codes now. The "2 job" formula works flawlessly, and by my untrained eye, your update looks like it should do the same but for some reason it's not quite right. The effort is much appreciated, but I don't expect you to continue to mess with this thing. You've given me more than enough help so that I can mess with it myself.

 

[Roderick_E]

Thank you, Sir. For some reason the updated formula is kicking back an incorrect result. It seems to only be grabbing one of the job codes now. The "2 job" formula works flawlessly, and by my untrained eye, your update looks like it should do the same but for some reason it's not quite right. The effort is much appreciated, but I don't expect you to continue to mess with this thing. You've given me more than enough help so that I can mess with it myself.

Well darn, works perfectly for me. Sorry it doesn't for you. In the cell I have Job A, Job B, Job C
Anyhow, thanks for letting me tinker with problem. Hope you get it working.