Google-sheets – Multipliers In Google Sheets

formulasgoogle sheets

I'm trying to apply multipliers to rows within a column total, having issues..
I've tried multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck..

Search attempts included (via both Google and Bing) "Google Sheets – Multiply Rows/Columns By Given Cell" in a variety of wordings; to which the most relevant result has always been "How to Multiply Numbers in Google Sheets"

I have a table that looks like this:

Totals      =            =        5 =        1
==============================================
Multiplier  =  Row Label = Column 1 = Column 2
==============================================
         0  =            =        1          0
         1  =            =        1          1
         2  =            =        2          0

As you can see in the example, I've applied the multipliers to demonstrate what I'm looking for.
What I'm looking for is for the "Totals" Row to reflect the "Multiplier" associated with the given column and row..

If possible, I'm trying to do this without applying any custom functions..

Best Answer

I believe what you're looking for is sumproduct.

In the totals cell, you would use

=sumproduct(multiplier_array, column_array)

Related Solutions

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total

Google-sheets – Google Sheets formula every x rows, assign incrementing cell value

Please try, 1 in A2 and in A3 copied down:

=if(mod(row(),7)=2,A2+1,A2)

Best Answer

Related Solutions

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

Google-sheets – Google Sheets formula every x rows, assign incrementing cell value

Related Topic