ARRAYFORMULA MMULT TRANSPOSE giving the wrong calculation result

Question

I have the following formula and I can't seem to find out what's wrong as it's producing wrong result.

=IFERROR(ARRAYFORMULA(MMULT(IFERROR($H$2:$O*1/ISNUMBER($H$2:$O*1),0), TRANSPOSE(IFERROR(($H$2:$O$2 ^ 0)/ISNUMBER($H$2:$O$2 ^ 0),0))) * VLOOKUP($G$2:$G,LookupSheet!$A$2:$B$6,2,FALSE)))

Sample Google Sheet data: https://docs.google.com/spreadsheets/d/1YNv2Et8l1SXh_KAQB2TDz4iaIieDqhkzVmYT0oePtb4/edit?usp=sharing

In the sample, ARRAYFORMULA SUMIF gives the correct result but ARRAYFORMULA MMULT TRANSPOSE doesn't. I can't use ARRAYFORMULA SUMIF due to poor performance in a 30,000 rows (growing) x 22 columns spreadsheet and it's affecting all my other spreadsheets which utilise the data using IMPORTRANGE.

Appreciate if someone can look into this.

Thank you in advance.

Answer 1

use:

=ARRAYFORMULA(MMULT(IFERROR(INDIRECT("H2:O"&MAX((G2:G<>"")*(ROW(G2:G))))*1, 0), 
 TRANSPOSE(COLUMN(H:O))^0)*
 VLOOKUP(INDIRECT("G2:G"&MAX((G2:G<>"")*(ROW(G2:G)))), LookupSheet!A2:B, 2, 0))