CODEWITHSUNDEEP
google-sheetsarray-formulas
tomm
tomm

Reputation: 321

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

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!

UPDATE:

If someone will look for the solution, the best one is Mike's solution. Take a look at picture what he has added.

I have just extended it to filter L and M columns and it does not matter how long columns are. Of course L and M columns have to have the same number of elements ;)

=SUM(MMULT(ARRAYFORMULA(--(G2:G=TRANSPOSE(FILTER(M2:M;ISTEXT(M2:M))))*(H2:H));FILTER(L2:L;ISNUMBER(L2:L))))

Upvotes: 0

Views: 100

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15318

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 enter image description here

Upvotes: 1

IMCSAM
IMCSAM

Reputation: 32

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. :)

Upvotes: 2

Related Questions