CODEWITHSUNDEEP
google-sheetsfilterlambdasumgoogle-sheets-formula
Oloap
Oloap

Reputation: 35

Using Arrayformula to sum columns in multiple sheets

I'm try to sum columns(F2:F) in multiple sheets.

Here's my current formula.

=ARRAYFORMULA(IF(ISBLANK($A$2:$A), ,QUERY({PROPER(FLATTEN(Romar!$B$2:$B, Angelo!$B$2:$B, Ayyan!$B$2:$B, Edwin!$B$2:$B)), FLATTEN(Romar!$F$2:$F, Angelo!$F$2:$F, Ayyan!$F$2:$F, Edwin!$F$2:$F)}, "SELECT SUM(Col2) WHERE Col1 = '" & $A$2:$A & "' LABEL SUM(Col2) ''")))

But it gives me the same result in the entire column.

enter image description here

I want the result to be the sum of the person in column(A2:A) each row.

enter image description here

Thank you!

Here's the sample sheet. The desired result should be the total amount released for the customer at A2:A.

https://docs.google.com/spreadsheets/d/1b3bFQzaOk2z3GAUh7mDEjBCmyRgDhbf8tpNDYX60QN8/edit?usp=sharing

Upvotes: 1

Views: 250

Answers (2)

player0
player0

Reputation: 1

try:

=BYROW(A2:A, LAMBDA(x, IF(ISBLANK(x),, IFERROR(
 SUM(FILTER({Romar!F:F; Angelo!F:F; Ayyan!F:F; Edwin!F:F}, 
            {Romar!B:B; Angelo!B:B; Ayyan!B:B; Edwin!B:B}=x)), 0))))

enter image description here

Upvotes: 2

Martín
Martín

Reputation: 10177

EDIT: One option is to put all this into one formula, but to make it a little more easy to manage, I've set four columns, one per sheet, with this formula (Col F to J):

=byrow($A2:$A,lambda(each,if(isblank(each),,SUMIF(Indirect(I1&"!$B$2:$B"),each,Indirect(I1&"!$F$2:$F")))))

And then, just a sum

Or just put all this in one formula (Col K):

=byrow(A2:A,lambda(each,if(isblank(each),,SUMIF(Romar!$B$2:$B,each,Romar!$F$2:$F)+SUMIF(Angelo!$B$2:$B,each,Angelo!$F$2:$F)+SUMIF(Ayyan!$B$2:$B,each,Ayyan!$F$2:$F)+SUMIF(Edwin!$B$2:$B,each,Edwin!$F$2:$F))

I'm giving a potential answer, given that it would have been ideal to try it in a sample sheet. If it doesn't work, share one and I can try it ;)

=byrow(A2:A,lambda(each,if(isblank(each),,SUMIF({Romar!$B$2:$B;Angelo!$B$2:$B;Ayyan!$B$2:$B;Edwin!$B$2:$B},each,{Romar!$F$2:$F;Angelo!$F$2:$F;Ayyan!$F$2:$F;Edwin!$F$2:$F}))))

Let me know!

Upvotes: 2

Related Questions