Search code examples
google-sheetsfilterlambdasumgoogle-sheets-formula

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

Solution

  • 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