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.

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

Thank you!

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

CodePudding user response：

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!