Excel Formula changes values after a day or so

Question

I have a bunch of text strings separated by commas in a cell (e.g., in COLUMN A here).

Column A	Column B
AA,AB,AC	??
AC,AZ,BB	??
AB,BB,CD	??

Each one of these texts is associated with values in a separate sheet called LEGEND.

Column A	Column B
AA	15
AB	20
AC	35
BA	90

I want the sum of the values (in Column B on first sheet) associated with the text strings. I have this Vlookup formula in my excel sheet at column B on first sheet =SUM(VLOOKUP(TEXTSPLIT(A1,,","),LEGEND!$A$2:$B$4,2,FALSE)).
It gives me the correct SUM but the issue is that when I re-open my file after a day, it only returns the first value and NOT the SUM. e.g., when I run the formula first time, it gives me 70 for the first one but after a day when I re-open my file, the value is already replaced with 15. Please help.

I have tried Mutiple formulas to fix this, but it didn't help.

Answer 1

Well, did this and saved, closed & reopened and still gives results:

SUM(SUMIFS(B$9:B$12,A$9:A$12,TEXTSPLIT(A2,,",")))

Just to prove the points I made in the comment, see: