Google sheets ArrayFormula - complex row equation repeats first result in every cell in column

Question

I need to sum a range of cells in each row but the cells have text that has to be ignored. For example, a cell may be 2= Moderate and I need the "2" for the sum.

If I use this formula, for one row, I get the correct result:

=SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2), "[^\d]+", "|"), "|"))

Now I want to use ArrayFormula to repeat this formula for every row. With everything I've tried, I get the result of the first row repeated for every row. Example: if the result of row 2 is 14, then every row will show 14.

Here is the simplest formula I have tried:

=ArrayFormula(IF(ISBLANK(F2:F),"",
    SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2), "[^\d]+", "|"), "|"))
))

For the innermost range that is not getting changed to represent the current row, I have tried using indirect with relative references CONCATENATE(INDIRECT("R[0]C6:R[0]C28")) with the same result.

Is there a way to get ArrayFormula to work here?

Edit:
Adding link to demo version of sheet: https://docs.google.com/spreadsheets/d/17OYq3tjP1A1H8SPYAoAAlxOlrsFV2R5CQT3MNc7ZkMo/edit?usp=sharing

Answer 1

use:

=ARRAYFORMULA(MMULT(IFERROR(REGEXEXTRACT(
 INDIRECT("F2:AB"&MAX((ROW(A2:A)*(A2:A<>"")))), "^\d+")*1, 0), 
 TRANSPOSE(COLUMN(F:AB))^0))

Answer 2

It is always difficult to write formulas without seeing your sheet, data or layout. But based solely on what you've provided, you can try this:

=ArrayFormula(IF(F2:F="",, MMULT(IF(ISNUMBER(F2:AB), F2:AB, 0), SEQUENCE(COLUMNS(F1:AB1), 1, 1, 0))))

MMULT performs matrix multiplication, which is difficult to explain but which essentially multiplies every element of one matrix by every element of another and adds the results by row.

Each element of each matrix must be numeric. So the formula creates the first matrix with an IF statement that replaces anything in the requested range that is a number with itself and anything that is not a number (i.e., text or blanks) with a 0. The SEQUENCE forms the necessary second matrix by creating a vertical "stack" of 1's that is the same width as the first matrix. And since anything multiplied by 1 is the original number, we get the correct result.

ADDENDUM:

After reading the additional comment by the OP and seeing a sample sheet, this is the MMULT setup needed:

=ArrayFormula(MMULT(VALUE(REGEXEXTRACT(FILTER(F2:AB, F2:F<>""), "\d+")), SEQUENCE(COLUMNS(F1:AB1), 1, 1, 0)))