Converting a letter into a dollar amount

HansVogelaar 

Hi Hans.

Best wishes for the New Year.

You caught me out for a moment, in that I thought from the colours that you were using a Table.  In which case the headers would be text and SUMIFS would return 0.

To get around this, I defined a helper range 'amountHeader' from the headers 'amount' using

= VALUE(REPLACE(amount,1,1,""))

so a dynamic array formula would be

= BYROW(selection, LAMBDA(s,
    SUMIFS(amountHelper#, s, "x")
  ))

Mind you my first thought had been to use XLOOUP

= BYROW(selection, LAMBDA(s,
    XLOOKUP("x", s, VALUE(REPLACE(amount,1,1,"")),"")
  ))

though that would not give the same result if multiple "x"s appeared within a row.

Then the thought occurred to me that not everyone uses dynamic array formulas all the time (difficult to believe I know), so to insert the result into the source Table

= SUMIFS(amountHelper#, Table1[@] selection, "x")

with an explicit intersection of the current row and the user selection of "x"s.

Amazing how much of a meal one can make of an apparently simple question!