Auto- Calculate values of text strings

[Gesyca_is_joy]

ok i have a similar question that I can't find the answer to.

Say i have a list in my sheet "Value" and it gives a value for every letter in the alphabet in a list setup across two columns, the letters of the alphabet in Column A (Rows 1-26) and their corresponding values in Column B, rows 1-26. (Shown Below) then in column D i have a list of words, and in column E i want to determine the value of that word based on the value of the letters in my list; how would i write that formula?

an example would be the word "Blue" would have a value of 7 (B=3, L=1, U=2, E=1), but i want to write a formula so that i can add more words to my list in column D and just drag the formula down to auto-calculate the value.

[TABLE="width: 87"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Letter[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]V[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]W[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]6

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

Yes, and that's what i originally tried; apologies, I am usually pretty good with writing my formulas but I have the hardest time with formulas that deal with text characters and i'm having trouble learning; i also can't write VBA.

I adjusted the original post answer to the following formula:

=SUM(SUBTOTAL(9,OFFSET($I$2,CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-65,0)))

Where A2 is the word and $I$2 is the location for numerical values based on letter. i then used Ctrl + Shift + Enter to make it an array formula. But every results is off by 1 so "ALL" should be 18 and is returning a value of 19; and that's consistent all the way down. Because i don't understand the mechanics of the formula I can't break it down piece by piece to troubleshoot and correct it the way i normally would. If possible I'd love to learn the logic behind it so i can edit it and use it when my word list gets more complex; I don't like to just straight copy and paste but I need to complete this task and I figure once I do that i can then save a duplicate copy where i can try to reverse figure out the formulas.

Isn't that essentially the same as your question in post 1, just with a different table? If so, the formula from post 2 should work.

 

[Gesyca_is_joy]

Apologies, somehow my formulas, even though dealing with strictly whole numbers, are forming decimal percentages and that is why the calculations are OFF in the results. this I can fix. thank you so much for all your help! If there are any good resrouces to learn how to caluclate with text strings please let me know; i'd love to learn how to do this myself instead of always asking for help.