Sum of each cell having text string required in separate cell.

And as for dosydos's solution, the Name Manager function you create can (and should be) relative to the current cell you're in.

https://superuser.com/questions/2533...were-a-formula (See answer from Laurentiu Mirica on Jun 27 '14 at 13:08)

Let's assume your string is in B3, and you're currently active cell is C3. When you go into Name Manager and create the new name (e.g. eval) and set the 'Refers to:' field to =EVALUATE(B3), you can then use the function

=eval

anywhere in the spreadsheet and it will reference the cell immediately to the left of that cell. (If you use it in column A you'll get an error, of course.)