Google Sheets - Calculate sum of row results - by using one-liner formula-CodePudding

In some column have formula like below:

=SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M2);0);L2))

I have expanded it in 3 rows (A1:A3):

=SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M2);0);L2))
=SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M3);0);L3))
=SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M4);0);L4))

And I'm getting right results. Then I want to get sum of those. But I need to create all of above in one line. I mean - I don't want to calculate above separately and then do something like SUM(A1:A3).

I was trying with ARRAYFORMULA() but with no success, yeah, how hard can it be, right?

Have a nice evening!

CodePudding user response：

You should be able to nest the evaluations inside a SUM, like the below and get a SUM of the SUM's so to speak.

=SUM(SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M2);0);L2)),SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M3);0);L3)),SUM(MMULT(IFNA(FILTER($H$2:$H;$G$2:$G=M4);0);L4)))

There probably is a more efficient way, but why over complicate it. :)

CodePudding user response：

Try this

=sum(mmult(arrayformula(--(G2:G=transpose(M2:M4))*(H2:H)),L2:L4))

the formula =arrayformula(--(G2:G=transpose(M2:M4))*(H2:H)) will give you a matrix as follows, then apply mmult