I want to perform a sum of a given string ('ABCDEF') based on its pre-assigned values. i.e first I want to assign values to each character and then calculate the total value of the string having pre-assigned characters. Is it possible to carryout this function in excel? eg. is below
A=2, B=5, C=8, D=1, E=1, F=2
sum of 'ABCDE'=17
sum of 'FAC'=12
So, this is what I have tried,
• Formula used in cell E1 --> Applicable to Excel 2021 & O365 Users Only
=SUMPRODUCT(VLOOKUP(MID(D1,SEQUENCE(LEN(D1)),1),$A$1:$B$6,2,0))
• Formula used in cell F1 --> Applicable to All Users
=SUMPRODUCT(VLOOKUP(MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),$A$1:$B$6,2,0))
Depending on Excel Version may or may need to press CTRL
+ SHIFT
+ ENTER
for the above formula!
The above formula(s) works well when used for one Mathematical
Operator, however if you want to use Multiple Operator, then I assume Defining Named Ranges for each of those characters and then use them for Mathematical
functions, shall be an easy one!