Subtotals for each group in Google Sheets

This is the result:

You get subtotal sums for each of the selected columns.

Usage in Sheets:

=subtotalSums(A2:G31,{1,2},{6,7})

Sample Spreadsheet:

https://docs.google.com/spreadsheets/d/16iDOO2otZmPVfpL75dCWR34CeiDgcdcZSNoS7j7hAVE

And this is the code:

// function test_SubTotals() {
//   var data = [
//     ['a', 'c', 'c', 5, 1],
//     ['a', 'b', 'c', 5, 1],
//     ['a', 'c', 'c', 5, 1],
//     ['b', 'x', 'c', 5, 1],
//     ['b', 'b', 'c', 5, 1],
//     ['a', 'd', 'f', 5, 1],
//     ['b', 'd', 'f', 5, 1]
//   ];
//   var groupColumns = [[1,2]];
//   var totalColumns = [[4,5]];
//   console.log(subtotalSums(
//     data, 
//     groupColumns, 
//     totalColumns));
// }

/** subtotalSums
 * 
 * Creates new data with subtotals
 * 
 * @param {Array[][]} data
 * @param {Array[][]} groupColumns
 * @param {Array[][]} totalColumns
 * 
 * @return {Array[][]} data with subtotals
 * 
 * @customfunction
 */
function subtotalSums(data, groupColumns, totalColumns) {

  // adjust options
  if (!Array.isArray(groupColumns)) {
    groupColumns = [[groupColumns]];
  }
  if (!Array.isArray(totalColumns)) {
    totalColumns = [[totalColumns]];
  }
  groupColumns = flatten2DArray_(groupColumns);
  totalColumns = flatten2DArray_(totalColumns);
  var column2Index_ = function(val) {return val-1; }
  groupColumns = groupColumns.map(column2Index_);
  totalColumns = totalColumns.map(column2Index_);


  var sortedData = sort2dArrayByArrayOfColumnNumbers_(
    data, groupColumns );

  var keys = [], maps = [];

  /** 
   * Subtotals = sum
   */
  var sum2Values_ = function(a, b) {
    if (typeof a !== 'number') {
      a = 0;
    }
    if (typeof b !== 'number') {
      b = 0;
    }
    return a+b;
  }

  /**
   * get the total by each row, group
   * 
   */
  var getRowTotals_ = function(row, initialTotals, groupIndexes) {
     var currentTotal = [];
     var indexesReturn = groupIndexes.concat(totalColumns);
     for (var i = 0; i < row.length; i++) {
       if (indexesReturn.indexOf(i) > -1) {
         currentTotal.push(row[i]);
       } else {
         currentTotal.push('');
       }
     }

    // sum current and initial totals
    var totalIndex = -1;
    for (var i = 0; i < totalColumns.length; i++) {
      totalIndex = totalColumns[i];
      currentTotal[totalIndex] = sum2Values_(
        currentTotal[totalIndex],
        initialTotals[totalIndex]
      )
    }

    return currentTotal;
  }


  /**
   * adds Total values to the map
   */
  var setMapOut_ = function(map, row, key, groupIndexes) {
    if (!map) {
      map = {};
    }
    var initialTotals = map[key] || {}; 
    map[key] = getRowTotals_(
      row, 
      initialTotals, 
      groupIndexes);
    return map;
  }


  /** 
   * get sery of keys
   * and run operation on maps
   */
  var columnKeysMap_ = function(row) {
    var keys = [], keysArr = [];
    var key = '';
    var groupIndex, groupIndexes = [];
    for (var i = 0; i < groupColumns.length; i++) {
      groupIndex = groupColumns[i];
      groupIndexes.push(groupIndex);
      keysArr.push(row[groupIndex]);
      key = keysArr.join('|');
      keys.push(key);
      maps[i] = setMapOut_(maps[i], row, key, groupIndexes);
    }
    return keys;
  }

  var arrKeys = [];
  for (var i = 0; i < data.length; i++) {
    keys = columnKeysMap_(sortedData[i]);
    arrKeys.push(keys);
  }

  var result = [], mountedKeys = [];

  /**
   * add final subtotals and rows to the result
   */
  var add2Result_ = function(keys, row) {
    var subRes = [];
    var map, mountedi, key;
    for (var i = 0; i < keys.length; i++) {
      map = maps[i];
      key = keys[i];
      mountedi = mountedKeys[i] || {};
      if (!(key in mountedi)) {
        // add subtotals to the front of list
        subRes.push(map[key]);
        mountedi[key] = true; // save to mounted
      }
      mountedKeys[i] = mountedi;
    }
    subRes.push(row);
    result = result.concat(subRes);
  }


  for (var i = 0; i < data.length; i++) {
    keys = arrKeys[i];
    add2Result_(keys, sortedData[i]);
  }

  // console.log(JSON.stringify(maps, null, 2));

  return result;
}

// Array Flattening Function
// function test_flatten2DArray() {
//   // Test
//   let arr = [[1, 2], [3, 4], [5, 6]];
//   console.log(flatten2DArray_(arr)); // [1, 2, 3, 4, 5, 6]
// }
function flatten2DArray_(arr) {
  var flattenedArray = [];
  for (var i = 0; i < arr.length; i++) {
    for (var j = 0; j < arr[i].length; j++) {
      flattenedArray.push(arr[i][j]);
    }
  }
  return flattenedArray;
}

// function test_sort2dArrayByArrayOfColumnNumbers() {
//   // Test
//   let arr = [
//     ['a', 'c', 'c', 5, 1],
//     ['a', 'b', 'c', 5, 1],
//     ['a', 'c', 'c', 5, 1],
//     ['b', 'x', 'c', 5, 1],
//     ['b', 'b', 'c', 5, 1],
//     ['a', 'd', 'f', 5, 1],
//     ['b', 'd', 'f', 5, 1]
//   ];
//   var col = [1, 2]
//   console.log(sort2dArrayByArrayOfColumnNumbers_(arr, col));
// }
/** 
 * Sort 2D array by array of column numbers
 *
 * @param {Array[][]} arr 2D array to be sorted
 * @param {Array[]} col Array of column numbers to sort by
 *
 * @returns {Array[][]} The sorted 2D array
 */
function sort2dArrayByArrayOfColumnNumbers_(arr, col) {
  var colLength = col.length;
  arr.sort(function(a,b) {
    for (var i = 0; i < colLength; i++) {
      var colIndex = col[i];
      if (a[colIndex] < b[colIndex]) {
        return -1;
      }
      if (a[colIndex] > b[colIndex]) {
        return 1;
      }
    }
    return 0;
  });
  return arr;
}

Notes:

1. This is possible to achieve with simple formulas, or with array formulas. I’ve chosen the script because I wanted it to be faster for the end user. And also the script gives more flexibility on the possible output.
2. This was the first time I’ve used Open AI to write code for me. And it does simple tasks very well. Guess, what functions were written by AI?

About the author

AI, why do people use subtotals in Spreadsheets?

— Subtotals are used in spreadsheets to quickly and easily calculate the total of a set of values within a given range. Subtotals are helpful when analyzing data within a spreadsheet, and can be used to quickly identify the sum of related data points. Additionally, subtotals can be used to quickly find the overall sum of the data and to compare different sections of data in order to draw conclusions.

Sort array by elements position

If you want to preserve the original position of grouped elements in the array, please change the sorting function in the code to:

// var test_sortMultiDimArrayByElementPosition = function() {
// var arr = [
//   [5, 'banana', 2],
//   [2, 'mango', 3],
//   [1, 'apple', 1],
//   [3, 'banana', 1],
//   [4, 'apple', 3],
//   [5, 'banana', 2]
// ];

// var sortedArr = sortMultiDimArrayByElementPosition_(arr, [2, 1, 3]);
// console.log(sortedArr);
// }
/** 
 * Sort 2D array by array of column numbers
 * sorts the same, but preserves the position of each column element
 *
 * @param {Array[][]} arr 2D array to be sorted
 * @param {Array[]} sortColumns Array of column numbers to sort by
 *
 * @returns {Array[][]} The sorted 2D array
 */
function sortMultiDimArrayByElementPosition_(arr, sortColumns) {
  var indexMaps = {};

  // minus 1 
  sortColumns = sortColumns.map(function(elt) {
    return elt - 1;
  });
  
  // Iterate through the array and build a map of index for each column
  for (var j = 0; j < sortColumns.length; j++) {
    var indexMap = {};
    for (var i = 0; i < arr.length; i++) {
      var key = arr[i][sortColumns[j]];
      if (!(key in indexMap)) {
        indexMap[key] = i;
      }
    }
    indexMaps[sortColumns[j]] = indexMap;
  }
  
  arr.sort(function(a, b) {
    for (var j = 0; j < sortColumns.length; j++) {
      var indexMap = indexMaps[sortColumns[j]];
      var indexA = indexMap[a[sortColumns[j]]];
      var indexB = indexMap[b[sortColumns[j]]];
      
      if (indexA < indexB) {
        return -1;
      } else if (indexA > indexB) {
        return 1;
      }
    }
    
    return 0;
  });
  
  return arr;
}

This will sort Arra[][] by columns and will leave the first elements at the top of the list. Say, you have a column:

 [banana, apple, apple, banana, mango]

The original code will group and sort it by the alphabet:

[apple, banana, mango]

In some cases, you may want to preserve the position, and the desired result is:

[banana, apple, mango]

↑ This is because the original array had “banana” in the first place.