Pulling two pieces of info from table into one cell

ExcelGuy12345

New Member
Joined
Feb 1, 2018
Messages
12
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Here it is, the magical formula :)

Code:
=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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ExcelGuy12345

New Member
Joined
Feb 1, 2018
Messages
12
Here it is, the magical formula :)

Code:
=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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,890
Messages
5,574,853
Members
412,623
Latest member
princexxa
Top