# How to get total count of matching counts from different, but matchingly named sheets in same spreadsheet?

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.

• Why don't you share a doc with us and show us the expected outcome? – Jacob Jan Tuinstra Jan 5 '14 at 19:35
• @JacobJanTuinstra: What do you mean with 'expected outcome' ? I don't mind if you want to be verbose to make me understand that one. – rautamiekka Jan 5 '14 at 23:12
• Well, I'm getting distracted by all the formulas and text you have written down. Sharing a doc (read spreadsheet), with the expected outcome (read: result), often tells more than a thousand words. – Jacob Jan Tuinstra Jan 6 '14 at 17:05
• @JacobJanTuinstra: Oh, I see. Sure, I can do that. See the `-ALL SHEETS-` tab, link after this, it has those formulas written and I wrote `iron ore` to each so that it's not forgotten what item is in question. docs.google.com/spreadsheet/… – rautamiekka Jan 6 '14 at 20:10
• @JacobJanTuinstra: The spreadsheet has changed many times during this time, mind you. – rautamiekka Jan 6 '14 at 20:34

## 1 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].length > 0) {
if(uniqueNames.indexOf(a[j]) == -1) {
uniqueNames.push(a[j]);
}
allData.push([a[j],a[j],a[j]]);
}
}
}

// 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]) {
sum1 += sData[m], sum2 += sData[m];
}
}
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

• Works just perfectly ! Good work, and thanks. – rautamiekka Jan 13 '14 at 5:38
• Bug found: if there are multiple items with same name but different sell value, the values are appended together, screwing up the value total calculations. An example: §shackle§, 4 pieces, 0200300300 value of 1. This could be avoided by separating identically named items with different sell value. – rautamiekka Jan 24 '14 at 4:22
• Adding to the bug: The script has to know that items without a sell price set means it's not found out yet. Same item with different values just means they're not all of same value. All these differing groups need to be separate groups. – rautamiekka Jan 24 '14 at 14:24