Google Sheets – Sum Values Across Multiple Sheets IDs Automatically

I've shared a sheet for you to play with here. There is a fourth sheet to help write the second solution

The simple solution (with only numeric ids) is quite straightforward:

=MAX(Sheet1!A2:A,Sheet2!A2:A, Sheet3!A2:A)

(This assumes each column has a title)

The situation with a prefix works like this:

="R" & MAX(
  ARRAYFORMULA(IF(ISBLANK(Sheet1!B2:B), 0, 
                VALUE(RIGHT(Sheet1!B2:B, LEN(Sheet1!B2:B)-1)))), 
  ARRAYFORMULA(IF(ISBLANK(Sheet2!B2:B), 0, 
                VALUE(RIGHT(Sheet2!B2:B, LEN(Sheet2!B2:B)-1)))),
  ARRAYFORMULA(IF(ISBLANK(Sheet3!B2:B), 0, 
                VALUE(RIGHT(Sheet3!B2:B, LEN(Sheet3!B2:B)-1))))
)

What I'm doing here is:

1. Use an arrayformula to repeat a function across each of the cells in a range.
2. Check that cell is not blank as B2:B means every cell from B2 down in the B column
3. If it is blank, assign it 0, which will mean the returned range is guaranteed to have values
4. Otherwise, remove the first character from "R...", then use VALUE to convert it to a number
5. Repeat for each sheet
6. Get the MAX value from the resulting ranges
7. Add R to the front

This assumes the prefix is constant and only one character.

If the names of sheets were known, you'd be able to do something like

={filter(Sheet1!C2:C, len(Sheet1!C2:C)); filter(Sheet2!C2:C, len(Sheet2!C2:C)); ... }

to join all of those together.

But since new Sheets will be added, and you don't know what names they will have, you need a script. I can think of two approaches: (a) the script will collect the data from C-columns itself; (b) the script will update the formula (of the above form) that will do the rest. The second one seems easier to code so I'll go with that.

function updateFormula(e) {
  if (e.changeType === 'INSERT_GRID' || e.changeType === 'REMOVE_GRID') {
    var sheets = e.source.getSheets();
    var formulaBits = [];
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetName() !== '🌴') {
        var quotedName = "'"+sheets[i].getSheetName()+"'";
        formulaBits.push('iferror(filter('+quotedName+'!C2:C, len('+quotedName+'!C2:C)))');
      }
    }
    var formula = '={'+formulaBits.join('; ')+'}';
    e.source.getSheetByName('🌴').getRange('C1').setFormula(formula);
  }
}

To deploy:

1. Open Tools > Script Editor and put the above code there. Click Save.
2. Go to Resources > "This project's triggers" and create a trigger with parameters "from spreadsheet", "on change".

You may want to add a sheet yourself to make the script run the first time; after that it will update the formula in cell C1 of sheet 🌴 whenever a new sheet is added or a sheet is removed.

In case of renamed sheets, the software takes care of renaming itself, adjusting the formula automatically.