Arrayformula Mmult Transpose Giving the Wrong Calculation Result
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...
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:
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.
1 Answer
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))
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
