Search code examples
google-sheetssum

Google Sheets Summary of other sheets (based on list of sheets)

I have a googlesheet with multiple base sheets : Base1, Base2, Base3 etc. which could vary over time. (Add a new base, remove a base etc.) I have a summary sheet. The summary sheet and Base Sheets are identical.

In the summary sheet I want cell

B2 = Base1!B2 + Base2!B2 + Base3!B2 and so on

But I want it to populate from the list of Bases in the Bases sheet what I tried is

=SUM(indirect(Bases!$A$1:$A$3&"!"&ADDRESS(ROW(),COLUMN()),true))

But it only takes on the first Base's values.

This works in excel though as expected, but not in google sheets.

I dont want to update the summary sheet's formalas each time I add or remove a base.

Here is a dummy mockup link to sample sheet

Solution

  • Use REDUCE, like this:

    =REDUCE(,Bases!A1:A3,LAMBDA(sum,tab,ARRAYFORMULA(sum+INDIRECT(tab&"!B2:M23"))))

    This formula produces the results for the whole table so you don't have to drag it.