How can I repeat a sumif calculation for several items with two criteria? In the example below, I would like to sum "Amount Sold" based on fruit and zone:

                                            Input

                              Desired Output (new sheet)

@Tanaike provided a perfect answer when there is one criterion (fruit):

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set your source sheet name.
  const dstSheetName = "Sheet2"; // Please set your destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const dstSheet = ss.getSheetByName(dstSheetName);
  const values = srcSheet.getRange(2, 1, srcSheet.getLastRow() - 1, 4).getValues();
  const res = [...values.reduce((m, [a,,, b]) => m.set(a, m.has(a) ? m.get(a)   b : b), new Map())];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);

Any suggestions for adding a second criterion (e.g. zone)? Thank you.

CodePudding user response：

From your showing sample input and output tables, how about the following modified script?

Modified script:

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set your source sheet name.
  const dstSheetName = "Sheet2"; // Please set your destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const dstSheet = ss.getSheetByName(dstSheetName);
  const values = srcSheet.getRange(2, 1, srcSheet.getLastRow() - 1, 4).getValues();
  const res = [...values.reduce((m, [a, , c, d]) => {
    const k = a   c;
    return m.set(k, [a, c, m.has(k) ? m.get(k)[2]   d : d]);
  }, new Map()).values()];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, the source values are retrieved from the columns "A", "C", "D" of the source sheet. And, the values are converted and the converted values are put into the destination sheet.