Sum of dynamic number of values derived from fomula

Question

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.

Answer 1

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; 
}

Screenshot

Example

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