How to add alphanumeric values in a speardsheet if they are comma separated?

Suppose, we have cells as below:

Cell  Value       Legend
==========================
 A1    1,A      // A = 1
 A2    2,AA     // AA = 2
 A3    3,L      // L = -1
 A4    4,N      // N = 0

I want the total to be calculated separately in other cells as:

A5  = SUM(1, 2, 3, 4)          = 1 + 2 + 3 + 4 = 10
A6  = SUM(1*A, 2*AA, 3*L, 4*N) = 1 + 4 - 3 + 0 = 2

Considering it may require separate functions in App Script, I tried to use SPLIT and SUM them, but it's not accepting the values. I asked a related question: How to pass multiple comma separated values in a cell to a custom function?

However, being a novice in spreadsheet, I am not sure if my approach is correct.
How to add alphanumeric values separately as stated above?

Solution 1:

you can create a small lookup table (legend) and then for the first sum try something like

=ArrayFormula(sum(iferror(REGEXEXTRACT(A1:A4, "[0-9-.]+")+0)))

and for the last

=sum(ArrayFormula(iferror(regexextract(A1:A4, "[0-9-.]+")*vlookup(regexextract(A1:A4, "[^,]+$"),D1:E4, 2, 0 ))))