The sum of products for a macro but is different in every time i get new data which is per month

Vansh Shah 0
Viewed 55

i need a formula or any help to find the total of (column I) i know a simple sum formula works but the problem is every month the data is different, the product is the same but the quantity and price change.

I put the appscripts code

​function sum_until_blank(cell_description, dummy) {
sheet = SpreadsheetApp.getActiveSheet();
var this_cell = sheet.getRange(cell_description);

if (this_cell.isBlank()) {
return 0;
}

var this_value = this_cell.getValue();
if (typeof this_value != "number") {
return 0;
}

next_cell = sheet.getRange(this_cell.getRow() + 1, this_cell.getColumn());
return this_value + sum_until_blank(next_cell.getA1Notation());
}

Copied from stack overflow question

This is how my data layout is and from above there is one row empty like to sheet is below

https://docs.google.com/spreadsheets/d/1X8nQOulWpEn4qVcsJKtnZp_5WLmAomzKkJ6LKFUyNKA/edit#gid=0

The link to the sheet, any help would be highly appreciated. I thought =sum(filter( could fix it but not very good at it and couldn't make it work , also an appscripts for this would also work (sum_until_blank) as above but don't know how to make it work automatically without manually adding =if(A:A="",sum_until_blank("I12",(I$12:I$999),B:B*E:E) also it just says loading data,i want to use apple scripts as a last resort so if theres a way to do it without appscripts, that would be fine too so any suggestion on how to make it work also suffice.

Thanks, Vansh

function google-sheets google-apps-script
1 Answers

Partial Sums

function partialsums() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 4;
  const vs = sh.getRange(sr, 9, sh.getLastRow() - sr + 1).getValues().flat();
  let a = 0;
  let v = 0;
  vs.forEach((s, i) => {
    v = parseInt(s);
    if (!isNaN(v)) {
      a += v;
    } else if (a != '' && a > 0) {
      sh.getRange(i + 4, 9).setValue(a).setNumberFormat("0.###############");
      a = 0;
    }
  });
}
user30hu0
Related