What functions can i use to count the letters in a box and compare it to a ref table

[s6656]

I like to do the following in excel, not sure which function would be easy to achieve my objective:

In a cell, when I enter apple, i want it to calculate the value of the cell based on the values attached to the alphabets in my reference table.

Eg in the above case, when the user enters apple, i want to calculate and display in another cell the value of apple.

I have assigned the following value to the letters in my reference table:
a= 2, p=3, l=1, e=5

So the value should be apple (2+3+3+1+5) = 19

Thanks for the advice.

 

[jim may]

(2+3+3+1+5) = 19
or
(2+3+3+1+5) = 14 ?

 

[Haseeb Avarakkan]

Hello, Welcom to the board!

Try

(2+3+3+1+5) = 14

Col D contains the Alphabets A-Z
Col E contains the associated numbers

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Apple</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">P</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">L</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">E</td><td style="text-align: right;;">5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">D:D,MID(<font color="Green">A1,ROW(<font color="Purple">A$1:INDEX(<font color="Teal">A:A,LEN(<font color="#FF00FF">A1</font>)</font>)</font>),1</font>),E:E</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jim may]

Looping through and accumulating data often Confuses me, I am trying to set up a UDF to accomplish this but am STUCK.. What's missing below to provide the 14?

Function SumLetters(Rng As Range) As Long
LRng = Len(Rng)
For i = 1 To LRng
T = Mid(Rng, i, 1)
M = WorksheetFunction.VLookup(T, Range("F4:G7"), 2, False)

TCZ =         'How can I accumulate and sum the M's ?

Next i
SumLetters = TCZ
End Function

 

[jim may]

Never mind - Brain Freeze gone...

Function SumLetters(Rng As Range) As Long
LRng = Len(Rng)
For i = 1 To LRng
T = Mid(Rng, i, 1)
m = WorksheetFunction.VLookup(T, Range("F4:G7"), 2, False)

'TCZ =         'How can I accumulate and sum the M's ?
TCZ = TCZ + WorksheetFunction.Sum(m)
Next i
SumLetters = TCZ
End Function