How to find the sum of certain column values by conditions from another one?-CodePudding

There are 2 columns. 1st one with values in date format DD.MM.YYYY, 2nd one with integer values. How to produce the sum of the numbers from the second column according to the conditions of matching the month from the first? For ex Im interesting in sum of the values that match MONTH () = 11?

CodePudding user response：

Monthly Sums

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1, 2).getValues();
  let sums = {pA:[]};
  vs.forEach((r => {
    let p = r[0].split('.').filter((e,i) => i > 0).reduce((a,c,idx) => {
      if(idx > 0) a  = '.';
      a  = c;
      return a;
    });
    if(!sums.hasOwnProperty(p)) {
      sums[p]= r[1];
      sums.pA.push(p);
    } else {
      sums[p]  = r[1]
    }
  }));
  let html = '<style> td,th{border: 1px solid black;}</style><table><tr><th>Month</th><th>Sum</th></tr>';
  sums.pA.forEach(p => {
    html  = `<tr><td>${p}</td><td>${sums[p]}</td></tr>`
  });
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),'Monthly Sums')
}

Data:

Sums Dialog:

CodePudding user response：

Suppose that your original data is in a sheet called Sheet1, that you have headers in A1 and B1 and that the data set you posted is in A2:B14 with nothing written in the cells A15:B. In a new sheet, enter this formula in cell A1:

=ArrayFormula(QUERY({DATE(YEAR(Sheet1!A2:A),MONTH(Sheet1!A2:A),1),B2:B},"Select Col1, SUM(Col2) WHERE Col2 Is Not Null GROUP BY Col1 LABEL Col1 'MONTH', SUM(Col2) 'TOTAL' FORMAT Col1 'mmm yyyy'"))

This QUERY will result in two headers (which you can change within the LABEL section of the QUERY if you like) and all results for all months/years present in the data set.

The QUERY acts on a virtual array which first converts all dates in Sheet1!A2:A into new DATEs based on the month and year from the original dates but all falling on the first of the month. This allows the SUMs to be grouped in the QUERY. Then the QUERY's FORMAT section converts those normalized dates to show you just the month and year portion.

Adjust the sheet name and referenced ranges to fit the sheet name and data range of your actual data.