Addition formula in a single cell

[thespardian]

Hi there
I have a four product code (i.e r,p,E,Y)in cell range A1:A4 and the price for that product is mentioned against each code in cell range B1:B4 (i.e 6.87, 9.18, 10.54, 22.73).
I am seeking for a formula for following purpose
if i enter multiple codes in a single cell (say Er,Yp,EY, EE, Yr, YYp,YEp etc it could be any combination of above four letter) the price should be appear in cell D1
Any help would be higly appriciated

 

[DanteAmor]

Re: Help regarding addition formula in a single cell please

i.e

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:140.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >r</td><td style="text-align:right; ">6.87</td><td > </td><td >Yep</td><td >22.73, 10.54, 9.18</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >p</td><td style="text-align:right; ">9.18</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >E</td><td style="text-align:right; ">10.54</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Y</td><td style="text-align:right; ">22.73</td><td > </td><td > </td><td > </td></tr></table>

If you put "Yep" in cell D1, what do you expect in cell E1?
The product code is always a single letter?
Are there only 4 codes or do you have more?

 

[thespardian]

Re: Help regarding addition formula in a single cell please

Thanks for your help, if i put "Yep" i expect 22.73+10.54+9.18

Its not certain. But it will remain between 1 to 6 characters(e.g E or EY, or EYr or EYpr, EErYYp)

Yes, i have only four codes and they are single character in a cell

 

[Aryatect]

Re: Help regarding addition formula in a single cell please

Hi,

Below should work for you:

Book1
	A	B	C	D	E
1	r	6.87		YEEP	52.99
2	p	9.18
3	E	10.54
4	Y	22.73
Sheet9

Cell Formulas
Range	Formula
E1	{=SUM(TRANSPOSE(INDIRECT(ADDRESS(MATCH(MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),$A$1:$A$4,0),2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[thespardian]

Re: Help regarding addition formula in a single cell please

It works like a charm. Thanks a lot for guidance. Stay blessed.

 

[Aryatect]

Re: Help regarding addition formula in a single cell please

Glad could help!

 

[Eric W]

Re: Help regarding addition formula in a single cell please

Another non-array option:

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]r[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]6.87[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]YEEP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]52.99[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]p[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]9.18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]10.54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Y[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]22.73[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUMPRODUCT(B1:B4*(LEN(D1)-LEN(SUBSTITUTE(UPPER(D1),UPPER(A1:A4),""))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[thespardian]

Re: Help regarding addition formula in a single cell please

Thanks a ton for your input, Eric, i'll definatly try it. Stay blessed