Google-sheets – How to get total count of matching counts from different, but matchingly named sheets in same spreadsheet

google sheets

I have an inventory spreadsheet with each chest having its own sheet with the sheet numbered and each of those sheets have identical syntax. Rows 1-4 are protected by the separator line, so data starts from row 5:

A|B|C|D|E|F|G|H
<slot number or nothing>|ITEM NAME|TOTAL COUNT OF ITEMS IN ONE STACK|VALUE OF 1 OF THE ITEMS IN COPPERS|TOTAL VALUE OF THE ITEMS IN COPPERS|<...> SILVERS|<...> GOLDS|<...> PLATINUMS

1|clay block|119|0|0|0|0|0
2|tin ore|99|75|7425|74.25|0.7425|0.007425

There's a sheet named -ALL ITEMS-, same syntax as the numbered sheets, which is supposed to make calculation of total counts of each item, for example iron ore. I already tried but couldn't figure out how to check each numbered sheet for the item and add up its count, in C column, to the C column in the -ALL ITEMS- sheet.

My attempt had 2 problems: if a sheet didn't have iron ore, it returned #N/A, and even if there was a row, it returned just that 1 row's TOTAL COUNT OF ITEMS IN THE STACK, not all of them if there was more than 1.

Dunno if you wanna see my code, but I show it just in case. These were in -ALL ITEMS-'s C column:

=INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 1), 0)=335
=INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 0), 0)=99
=INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0)=99 [because sheets 1 and 4 have iron ore, and 4 has it twice with different TOTAL COUNT IN STACK for each]

=SUM( INDEX('1'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('2'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('3'!C5:C9999, MATCH("iron ore", '3'!B5:B9999, 0), 0)+INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0) )=#N/A, error: Did not find value iron ore

EDIT 0: It'd speed up things very much if the item name would be auto-taken from the B column to the left instead of having to be specified in the commands, especially since there are -many- chests and more are coming steadily.

Best Answer

The code below will get all unique names from the sheet names 1 through 14 sheets and perform a summation.

Code

function getTotals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var uniqueNames = [], allData = [], output = [];

  // itterate through the sheets and retrieve info  
  for(var i=1; i<14; i++) {
    this.sh = ss.getSheetByName(i); 
    this.lRow = this.sh.getMaxRows();
    this.names = this.sh.getDataRange().getValues();
    var a = this.names;

    // gather unique names and data
    for(var j=4, jLen=a.length; j<jLen; j++) {
      if(a[j][1].length > 0) {
        if(uniqueNames.indexOf(a[j][1]) == -1) {
          uniqueNames.push(a[j][1]);
        }          
        allData.push([a[j][1],a[j][2],a[j][3]]);
      }
    }    
  }

  // sort all the data for efficient processing
  var sData = allData.sort(), sNames = uniqueNames.sort();

  // itterate throught the names and perform sum
  for(var l=0, lLen=sNames.length; l<lLen; l++) {
    var sum1=0, sum2=0;
    for(var m=0, mLen=sData.length; m<mLen; m++) {
      if(sNames[l] == sData[m][0]) {
        sum1 += sData[m][1], sum2 += sData[m][2];
      }      
    }
    output.push([sNames[l], sum1, sum2]);
  }
  return output;
}

Remark

Make a copy of the spreadsheet and goto Tools>Script editor

Example

I've created a copy of your file and added the solution to it: Sum Through Sheets

Related Topic