1

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 1 screenshot

Sheet 2:
sheet 2 screenshot

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

Totals screenshot

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.

migrated from stackoverflow.com Jul 23 '14 at 14:43

This question came from our site for professional and enthusiast programmers.

  • Sounds to me like a script would be needed to really achieve this for an arbitrary set of Values & Sheets. – StampedeXV Jul 23 '14 at 15:43
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

enter image description here

Example

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

  • 1
    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 '14 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.