Sum if Across Multiple Sheets in Google Sheets

Question

I need to calculate the SUM of 1 column on different sheets per line. Here is my example file - https://docs.google.com/spreadsheets/d/11KWjso_sLdHI-NWBk0Rtp-EnGfMyU6a3G143C1Tl6lE/edit?usp=sharing The С column on Sheet1 should calculate the sum of column T on all pages. I am trying to use the following formula, but it doesn't work: =SUMPRODUCT(SUMIFS(INDIRECT("'"&TO_TEXT(Rules!$D$1:$1)&"'!"&"T:T"),INDIRECT("'"&TO_TEXT(Rules!$D$1:$1)&"'!"&"A:A"),A2,INDIRECT("'"&TO_TEXT(Rules!$D$1:$1)&"'!"&"B:B"),B2)) It calculates the sum for 1st page only. Please help.

Unfortunately INDIRECT doesn't support iteration over an array in google-sheets. You may be helped by onEdit() for the Rules sheet. When you change the data in row 1:1 - the script will update the formulas in C column on Sheet1 — Sergey

Sergey Sergey · Accepted Answer · 2021-06-10T14:36:26

This code will help you

function onEdit(e) {
  let source = e.source,
      range  = e.range,
      col    = range.columnStart,
      row    = range.rowEnd,
      sheet  = range.getSheet();
  if (sheet.getName() == 'Rules' && row == 1 && col>2){
    let sheetNames = sheet.getRange('1:1').getDisplayValues()[0].filter(String).slice(3),
        formula = sheetNames.reduce((r,element)=>{
          return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
        },`={"SUM";ArrayFormula(if(A2:A<>"",`).replace(/\+$/,',))}');
    SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('C1').setFormula(formula)
  }
}

this script rewrites the formula in cell C1 on Sheet1 every time you change the data in row 1:1 on Rules

added ...

in order to change the formula to the one you want ={"SUM";ArrayFormula( if(A2:A<>"",ifs((SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))=2,"New Full Time",SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))<2,"Part Time",(SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))>2,"Full Time"),))} change a part of the code to this

...
   subFormula = sheetNames.reduce((r,element)=>{
     return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
     },`(`).replace(/\+$/,')'),
   formula = '={"SUM";ArrayFormula(if(A2:A<>"",ifs(' + 
                      subFormula + '=2,"New Full Time",' + subFormula + '<2,"Part Time",' + subFormula + '>2,"Full Time"),))}'
...

but I think the next formula will be easier and work faster ={"SUM";ArrayFormula( if(A2:A<>"",SWITCH((SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T)),0,"Part Time",1,"Part Time",2,"New Full Time","Full Time"),))} to insert it into the code, change the following

...
formula = sheetNames.reduce((r,element)=>{
  return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
},`={"SUM";ArrayFormula(if(A2:A<>"",SWITCH((`).replace(/\+$/,'),0,"Part Time",1,"Part Time",2,"New Full Time","Full Time"),))}');
...

but even faster would be a constant formula that can be written in the next column D

={"-Header-";ArrayFormula(if(C2:C<>"",ifs(C2:C<2,"Part Time",C2:C=2,"New Full Time",C2:C>2,"Full Time"),))}

Sum if Across Multiple Sheets in Google Sheets

1 Answers