Addition formula in a single cell

thespardian

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
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
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,678
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
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
 
Last edited:

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Re: Help regarding addition formula in a single cell please

Hi,

Below should work for you:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">r</td><td style="text-align: right;;">6.87</td><td style="text-align: right;;"></td><td style=";">YEEP</td><td style="text-align: right;;">52.99</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">p</td><td style="text-align: right;;">9.18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">E</td><td style="text-align: right;;">10.54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Y</td><td style="text-align: right;;">22.73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">{=SUM(<font color="Blue">TRANSPOSE(<font color="Red">INDIRECT(<font color="Green">ADDRESS(<font color="Purple">MATCH(<font color="Teal">MID(<font color="#FF00FF">D1,ROW(<font color="Navy">INDIRECT(<font color="Blue">"1:"&LEN(<font color="Red">D1</font>)</font>)</font>),1</font>),$A$1:$A$4,0</font>),2</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

thespardian

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
Re: Help regarding addition formula in a single cell please

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,742
Re: Help regarding addition formula in a single cell please

Another non-array option:

ABCDE
1r6.87YEEP52.99
2p9.18
3E10.54
4Y22.73

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
E1=SUMPRODUCT(B1:B4*(LEN(D1)-LEN(SUBSTITUTE(UPPER(D1),UPPER(A1:A4),""))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

thespardian

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
Re: Help regarding addition formula in a single cell please

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

Watch MrExcel Video

Forum statistics

Threads
1,098,863
Messages
5,465,120
Members
406,414
Latest member
Discorz

This Week's Hot Topics

Top