I am creating a game on Glideapps where users input a word and each letter of that word has a specific point.

For example:

E = 1 point

F = 2 points

G = 3 points

H = 4 points

I = 5 Points

Now for example if a user adds a word “HI”, they will get 9 points (H = 4 points + I = 5 points.)

I can easily achieve that by splitting the text and do a relationship with score sheet and do a roll up to get total points.

The problem is if a word has same letter for example

“HIGH”, the relationship doesn’t know that the letter “H” has been used twice therefore it doesn’t add up its score twice.

The problem is explained better on a loom video attached below.

Here is the formula I used in Google Spreadsheet to get what I want but can’t replicate the formula into glide:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IFERROR(VLOOKUP(MID(UPPER(C2),ROW(INDIRECT(“1:”&LEN(C2))),1),{“A”,1;“B”,2;“C”,3;“D”,4;“E”,5;“F”,6;“G”,7;“H”,8;“I”,9;“J”,1;“K”,2;“L”,3;“M”,4;“N”,5;“O”,6;“P”,7;“Q”,8;“R”,9;“S”,1;“T”,2;“U”,3;“V”,4;“W”,5;“X”,6;“Y”,7;“Z”,8},2,FALSE),0))), 1, 1)