Google-sheets – SUMIF date range and filter by value on Google Spreadsheet

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

There is really no way to use IMPORTRANGE within an array formula as well there isn't for IMPORTDATA and IMPORTXML. See my answer to Google Sheets: How can I combine multiple ImportXML results for multiple URLs into a single column?.

Another related question:

• Use of importdata with arrayformula