The code below will get all unique names from the sheet names 1
through 14
sheets and perform a summation.
Code
function getTotals() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var uniqueNames = [], allData = [], output = [];
// itterate through the sheets and retrieve info
for(var i=1; i<14; i++) {
this.sh = ss.getSheetByName(i);
this.lRow = this.sh.getMaxRows();
this.names = this.sh.getDataRange().getValues();
var a = this.names;
// gather unique names and data
for(var j=4, jLen=a.length; j<jLen; j++) {
if(a[j][1].length > 0) {
if(uniqueNames.indexOf(a[j][1]) == -1) {
uniqueNames.push(a[j][1]);
}
allData.push([a[j][1],a[j][2],a[j][3]]);
}
}
}
// sort all the data for efficient processing
var sData = allData.sort(), sNames = uniqueNames.sort();
// itterate throught the names and perform sum
for(var l=0, lLen=sNames.length; l<lLen; l++) {
var sum1=0, sum2=0;
for(var m=0, mLen=sData.length; m<mLen; m++) {
if(sNames[l] == sData[m][0]) {
sum1 += sData[m][1], sum2 += sData[m][2];
}
}
output.push([sNames[l], sum1, sum2]);
}
return output;
}
Remark
Make a copy of the spreadsheet and goto Tools>Script editor
Example
I've created a copy of your file and added the solution to it: Sum Through Sheets
As I was building this question, I figured out several ways to achieve this, so I went ahead and shared the information.
There are several ways to do this. The first is a variation on your original syntax, but using nested IF
statements instead of IF
and AND
:
=ARRAYFORMULA(SUM(IF(MONTH($A$1:$A$5)=MONTH(E1), IF(YEAR($A$1:$A$5)=YEAR(E1), $B$1:$B$5))))
The second uses the FILTER
function. This method will return a #N/A
error if FILTER
doesn't find any matches for the conditions. FILTER
takes each condition as a separate argument:
=SUM(FILTER($B$1:$B$5, MONTH($A$1:$A$5)=MONTH(E1), YEAR($A$1:$A$5)=YEAR(E1)))
The third uses INDEX
and SUMPRODUCT
:
=INDEX(SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(E2))*(YEAR($A$2:$A$6)=YEAR(E2))*$B$2:$B$6), 1)
In each of these examples, I assumed that the data were in columns A and B, the "pivot table" dates were in column E, and the aggregated data are placed in column F.
There might be a way to do this with the QUERY
function that provides an interface to the Google Visualization API Query Language, but I'm not sure. I don't know if such a query would dynamically update, either.
Best Answer
Use EDATE function
Set the first value in the cell to a specific date. Like 1/1/2020
Second cell set this formula:
Drag the value above to other cells.