Need a Google Sheets formula to sum the same cell across multiple tabs-CodePudding

I have a Google Sheet with many tabs. Each tab is named with a person's name (first last). Each person's tab has an integer number in cell A1. How can I write a formula in a summary tab that will sum all the integers in all the named tabs, without having to list all the tag names separated by ' 's? I need to do this for many different cells, and there are many named tags, so the effort to build all the formulas takes too long. Excel has a formula for this, but that formula doesn't work in Google Sheets.

CodePudding user response：

Workaround

You can create the formula synamically and copy to A1 but you need SheetTabs names of the Person's like this

=CONCATENATE("=",TEXTJOIN("   ",1, ArrayFormula(SUBSTITUTE("'@'!A1","@",FILTER(C2:C,C2:C<>"")))))

CodePudding user response：

Create a sheet named b (begin) and a sheet named e (end) which can be empty. These sheets will be before (b) and after (e) all the sheets you want to take into account in the formula.

Put =sum3D() in cells you want to add (same place as the number you want to add in the differents tabs)

Put this custom function in your script editor

function sum3D() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  var shs = ss.getSheets();
  var rng = ss.getActiveSheet().getActiveCell().getA1Notation()
  var sh1 = ss.getSheetByName('b').getIndex()
  var sh2 = ss.getSheetByName('e').getIndex()
  var result = 0
  for (var i = sh1; i < sh2; i  ) {
    result  = shs[i].getRange(rng).getValue()
  }
  return result
}

you can add a dummy argument as a check-box to actualize the computation =sum3D(checkbox)