Google Sheets dynamic formula to fetch sheet names and sum cell values across multiple sheets

1
votes

I am trying create an analysis spreadsheet in Google Sheets that sums the values from a table of 5 cells per row and 20 cells per column across multiple sheets into a results sheet.

Here’s a copy of the current working version - https://docs.google.com/spreadsheets/d/1bAWt_z5ijvuLVNh38fRzTnn0-DysHGzbilVsbKJPni4/edit?usp=sharing

I’m currently using a formula in my Results sheet to sum values over the 100 cells over sheets named P1-P8.

enter image description here

Cell 1 example: =SUM('P1'!C3+'P2'!C3+'P3'!C3+'P4'!C3+'P5'!C3+'P6'!C3+'P7'!C3+'P8'!C3)

enter image description here

enter image description here

Ideally this would work dynamically when sheets are named differently or at differently quantity.

I have a look at loops and array formulas but I’m not having much luck.

Is there a way to do this without using a strict sheet naming convention and defined number of sheets?

1
google-sheetsgoogle-sheets-formulagoogle-sheets-query
I've done something similair many moons ago using =QUERY you can query multiple sheets and then do some simple aggregations on the results. It's a bit of faff setting it up but once it's done it works. Best to share a sample spreadsheet for others to use – Umar.H
share a copy of your sheet with example of desired output – player0
Thank you. Updated now with working copy link. – Benjamin Parry

1 Answers

1
votes

In sheet Result, clear range C3 to G100, and in C3 write this:

=arrayformula(('P1'!C3:G100 + 'P2'!C3:G100 +'P3'!C3:G100 + 'P4'!C3:G100+ 'P5'!C3:G100 + 'P6'!C3:G100 + 'P7'!C3:G100 + 'P8'!C3:G100))

And for automatically, when you add more sheets with different naming, may be you can use macro function