I was asked for help with this and used a function I've not used in a while. I don't see much discussion of this so thought i'd mention.
problem: a range of numbers and letters. Each letter representing a number (for some undisclosed reason). A lookup table was available to provide the letter values (I assume a two column table, letter in first column, value in second). A single formula was required to sum the numbers plus looked up values. (reasons were supplied, none very compelling, but a challenge is a challenge!)
Eventual solution
<code>={=SUM(IF((ISNUMBER(A1:D6)),A1:D6,LOOKUP(A1:D6,F1:F6,G1:G6)))}</code>
It has a simplicity that was entirely lacking from what I tried before getting here.
problem: a range of numbers and letters. Each letter representing a number (for some undisclosed reason). A lookup table was available to provide the letter values (I assume a two column table, letter in first column, value in second). A single formula was required to sum the numbers plus looked up values. (reasons were supplied, none very compelling, but a challenge is a challenge!)
Eventual solution
<code>={=SUM(IF((ISNUMBER(A1:D6)),A1:D6,LOOKUP(A1:D6,F1:F6,G1:G6)))}</code>
It has a simplicity that was entirely lacking from what I tried before getting here.
