How to create total from the same value on different Google Spreadsheet

Question

I would like to have the sum of some values on different spreadsheet on one "total" spreadsheet. Usually I start myself to find a solution and then ask for some help, but here, I have just no idea how I can do such a things. I have created a "manual total" on a sheet, just to explain what I want. I have a sheet by month then I have a total sheet

And I would like this sheet to work automatically, month after month...

Answer 1

You may try:

=let(Σ,reduce(wraprows(,3,),tocol(E:E,1),lambda(a,c,let(Δ,{a;{indirect("'"&c&"'!A5:b25"),byrow(indirect("'"&c&"'!c5:ar25"),lambda(Σ,sum(Σ)))}},filter(Δ,index(Δ,,1)<>"")))),
     Γ,query(Σ,"Select Col1,Col2,sum(Col3) group by Col1,Col2 label sum(Col3) ''"),
     Ξ,unique(index(Γ,,2)),
     Δ,reduce(wraprows("skip_",3,),sequence(counta(Ξ)),lambda(a,c,{a;sort(filter(Γ,index(Γ,,2)=index(Ξ,c)),3,0);{"",index(Ξ,c),sum(filter(index(Γ,,3),index(Γ,,2)=index(Ξ,c)))}})),
     filter(Δ,index(Δ,,1)<>"skip_"))

• all the months tab names need be listed in Column_E (as in screenshot)
• some of the month tabs have column(s) until Column_AK & some of them till Column_AR. Have them all consistently until _AR for the formula to work w/out error code.

updated formula for fixes:

=let(Σ,reduce(wraprows(,3,),tocol('LEGENDE PROJET'!I2:I,1),lambda(a,c,{a;{indirect("'"&c&"'!A5:b25"),byrow(indirect("'"&c&"'!c5:ar25"),lambda(Σ,SUM(Σ)))}})),
     Γ,query(Σ,"Select Col1,Col2,sum(Col3) Where Col1<>'' group by Col1,Col2 label sum(Col3) ''"),
     Ξ,unique(index(Γ,,2)),
     Δ,reduce(wraprows("skip_",3,),sequence(COUNTA(Ξ)),lambda(a,c,{a;sort(filter(Γ,index(Γ,,2)=index(Ξ,c)),3,0);{"",index(Ξ,c),SUM(filter(index(Γ,,3),index(Γ,,2)=index(Ξ,c)))}})),
     filter(Δ,index(Δ,,1)<>"skip_"))