# Sum the totals for each product over all sheets in a spreadsheet

I'm trying to make a spreadsheet to take online orders from clients. I send them a price list, which they complete with their order and every time a client sends an order, it automatically adds a new sheet to the "master" spreadsheet. The added sheet is named with the mail from the client and the time they make the order.

The thing is, I want to sum the totals ordered for each product on the list, but sheets are added all the time, and with non predictable names. I have managed to make a list of the sheets using a script (actually I'm having problems making it update automatically), and then using indirect and sumif. But i have to use a sumif for every sheet in the list, and then again the number of sheets may variate. For example I use

``````=sumif(indirect(E5&"!B\$5:B"),B\$2,indirect(E5&"!F\$5:F"))
``````

where in E5 is the name of the first sheet of the list. Is there a way to use an array formula to aply the sumif formula, to all the sheets in the list? (The list will be in the E column)?

When you have many `sumif` formulas in your spreadsheet, this is usually a sign you need to use `query` instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead:

``````function queryFormula() {
var formula = "=query({";
var sheets = ss.getSheets();
for (var i=1; i<sheets.length; i++) {
formula = formula + "'" + sheets[i].getName() + "'!A5:E;";
}
formula = formula.replace(/;\$/, '}, "select Col1, Col2, sum(Col5) group by Col1, Col2")');
ss.getSheetByName("Totales").getRange("H2").setFormula(formula);
}
``````

The result of running this script is that cell H2 gets the formula

``````=query({'02/26/2016 20:33:08 gcrosta@gmail.com'!A5:E;'02/26/2016 19:23:17 leyla.s.rodriguez@gmail.com'!A5:E;'02/26/2016 17:26:37 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:26 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:17 iris2908@fibertel.com.ar'!A5:E;'02/26/2016 17:05:49 iris2908@fibertel.com.ar'!A5:E}, "select Col1, Col2, sum(Col5) group by Col1, Col2")
``````

which does everything else. The first parameter of the query is the array, which consists of the ranges A5:E put together, on top of one another.

The second parameter is a query string, which says to sum column 5 (which in the range A:E is E), grouping by columns 1, 2 (which are A,B).

The output looks like this:

``````+-------------------------------------------------+------+------+
| ACEITE DE oLIVA Escencias de la Tierra x 500 ml | 2215 |  582 |
| Aceite de Coco God Bless 325 g                  | 2488 |  900 |
| Aceite de Coco Refinado Napus 660 ml            | 2626 |  840 |
| Aceite de Coco Virgen God Bless 225 g           | 2479 |  900 |
| Aceite de Coco Virgen God Bless 500 g           | 2387 | 1500 |
+-------------------------------------------------+------+------+
``````

### Updating the formula

If you rename the script function "queryFormula" into `onOpen`, it will run every time you open the spreadsheet, thus ensuring you get the current set of sheets in the formula.