3

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
shivam
  • 596
  • 2
  • 9
  • It is certainly possible, but what attempt have you taken from your end. could you show us that? And why did you use `ms-office-script` tag? – Mayukh Bhattacharya Mar 29 '22 at 01:13
  • @MayukhBhattacharya I have been working on hit and trial method. I have been trying hard to find on internet and tried all the suggested possible options but hard luck. Its hard to mention all of them in the question. I think it could be a part of 'ms-office-script' so I have tagged it. – shivam Mar 29 '22 at 01:24

1 Answers1

2

So, this is what I have tried,

FORMULA_SOLUTION

• 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!

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • 1
    Thanks... This is exactly what I am looking for but somehow it doesn't work for me. I am exactly mimicking your formula but it shows #NAME? error. – shivam Mar 29 '22 at 01:59
  • @shivam what is your Excel Version? are you using **O365**? – Mayukh Bhattacharya Mar 29 '22 at 01:59
  • 1
    I am using "Microsoft® Excel® 2019 MSO (Version 2202 Build 16.0.14931.20128) 64-bit" – shivam Mar 29 '22 at 02:04
  • @shivam Check now, the 2nd Formula should work, the earlier one was for **Excel 2021** & **O365** Users – Mayukh Bhattacharya Mar 29 '22 at 02:05
  • 1
    Thanks @Mayukh your updated formula '(=SUMPRODUCT(VLOOKUP(MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),$A$1:$B$6,2,0)))' works perfectly in LibreOffice Sheet but doesn't work in MS Excel. Not sure what is the problem with the MS excel. It always show '2' as answer – shivam Mar 29 '22 at 02:14
  • @shivam there should not be any issue in Excel as well, you may need to press `CTRL` + `SHIFT` + `ENTER` – Mayukh Bhattacharya Mar 29 '22 at 02:15
  • Thanks Bhai, I tried ctrl+shift+enter but hard luck :( – shivam Mar 29 '22 at 02:19
  • @shivam wait I am sharing, I understood, why it happened, could you share me a `One-Drive` link in the comments – Mayukh Bhattacharya Mar 29 '22 at 02:21
  • Please find the link https://1drv.ms/x/s!Ambh7Xh1BFhYau9l01HS8u1_ysc?e=CvT1eH – shivam Mar 29 '22 at 02:34
  • 1
    Seems some problem is with my excel. in one drive it works perfectly but as I download it and open in excel it automatically converts to "2" – shivam Mar 29 '22 at 03:11