Sum of dynamic number of values derived from fomula

In short my question is this. If I use the SUM function (or some other function) that can take one or more arguments, is there some way I can pass in these arguments as an array which varies over some variable? For example, `SUM(CONCATENATE("1", A1))` and then have it go through a range in column A.

Longer explanation, my situation is this. I have a number of sheets in which there's a column for which I want the sum, based on the value of another column.

Sheet 1:

Sheet 2:

I have another sheet with the sums for values A and B. Using the `INDIRECT` function let me do this for Sheet 1:

The formula used is

`````` SUM(IFERROR(FILTER(INDIRECT(CONCATENATE("'",\$D\$2,"'!","A:A")),EQ(A1,INDIRECT(CONCATENATE("'",\$D\$2,"'!","B:B")))),0))
``````

As you can see, it yields the correct sum for values A and B for Sheet 1. What I'd like to do now is add values to column D as more sheets are added in the future, and have the sum calculated over all the sheets. Can I do this using formulas, or is there need for a scripting function? I've tried using `ARRAYFORMULA` but the end result is that the names of all the sheets get concatenated for the `INDIRECT` function, so that doesn't work.

I feel that there must be some way to define a variable in a formula and then have it evaluated for an array of values for that variable, but I can't quite figure it out.

• Sounds to me like a script would be needed to really achieve this for an arbitrary set of Values & Sheets. Jul 23, 2014 at 15:43

1 Answer

The script below will perform a summation throughout tabs, taking into account different strings, as shown in your question.

Code

``````function mySum(tabRange, strings) {
// small error handling
if(typeof tabRange === 'string') {
throw 'Multiple tabs are needed. Otherwise use SUM';
return
}

// get active spreasdsheet and create empty arrays
var ss = SpreadsheetApp.getActive(), aStr = [], output = [];

// push first result in array to control different strings
aStr.push(strings[0][0], 0);

// iterate through tabs
for(var i = 0, iLen = tabRange.length; i < iLen; i++) {

// get all data (at once) from tab
var data = ss.getSheetByName(tabRange[i][0]).getDataRange().getValues();

// iterate through strings
for(var k = 0, kLen = strings.length; k < kLen; k++) {

// create and set the summation per string
var strSum = 0;

// iterate through the data
for(var j = 0, jLen = data.length; j < jLen; j++) {

// if a match is found, then add to the strSum
if(data[j][1] === strings[k][0]) {
strSum += data[j][0];
}
}

// find index in array that matches a string
var index = aStr.indexOf(strings[k][0]);

// if found, then add to existing value
// else, create a new entry for that string
// so that it will be found the next time
if(index !== -1) {
aStr[index+1] += strSum;
} else {
aStr.push(strings[k][0], strSum);
}
}
}

// get rid off the strings
for(var l = 1, lLen = aStr.length; l < lLen; l += 2) {
output.push(aStr[l]);
}

// show the outcome
return output;
}
``````

Example

I've created an example file for you: Sum of dynamic number of values derived from fomula

• Outstanding! I eventually settled for creating a matrix of the sheets and values (in my third screenshot, after tabs would be the values A, B etc.) and have the total for each value per sheet in there, then using that column to get the total for that value and all sheets on the left. That was before I saw this answer. It works, but could become unwieldy for large sets of values/sheets. I'll remember this answer should I want the script in the future. Many thanks!
– G_H
Aug 1, 2014 at 9:20