Using Arrayformula to sum columns in multiple sheets

Question

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.

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

share a copy / sample of your sheet with an example of the desired output — player0
– player0, Commented Nov 10, 2022 at 20:12

player0 · Accepted Answer · 2022-11-11 12:40:26Z

2

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

edited Nov 11, 2022 at 12:40

answered Nov 11, 2022 at 0:32

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oloap Over a year ago

btw how to you type on the next row for the code inside a cell? I use CTRL+ENTER but the code returns to the previous row as soon as I switch cells.

player0 Over a year ago

@Oloap first click into fx bar and press CTRL+DOWNARROW then start typing the formula and whenever you need press CTRL+ENTER to initiate new line. keep in mind that fx bar remembers all your previous edits and reverts back so if you made some mistake and fx bar reverts your desired result back into the mistake then you need to change something alongside for example SUM to SuM so that your changes would apply

Martín · Accepted Answer · 2022-11-11 00:19:35Z

2

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!

edited Nov 11, 2022 at 0:19

answered Nov 10, 2022 at 12:42

Martín

10.4k2 gold badges7 silver badges16 bronze badges

2 Comments

Oloap Over a year ago

It gives an error that says argument must be a range. I also added a link to a sample sheet.

Oloap Over a year ago

It works! Thank you for your time and effort! :)

Collectives™ on Stack Overflow

Using Arrayformula to sum columns in multiple sheets

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related