Google Sheets – Sum Product of Two Columns

google sheets

Here's the situation:

ITEM COST CUST1  CUST2  CUST3    TQTY

foo  $0.5     1      0    0.5     1.5
baz  $1.0     2      1      0       3
bar  $1.5   0.5      0    0.3     0.8

SUBT       $2.75 $1.00  $0.75   $4.50

Simple, right? The SUBT row should have the contents of

CUST1*COST, CUST2*COST, CUST3*COST

for each row. And the TQTY column has the sum of

CUST1+CUST2+CUST3

for each row.

At least it SHOULD be simple, but auto-fill keeps screwing me. TQTY is easy, of course, but I can't for the life of me figure out how to use a formula to give me the sum of the product of two columns cell by cell. In particular, I need to do this in a way that will be user-manageable for someone wanting to insert rows or columns in the middle and have it continue to "just work".

Best Answer

This will sum the quantities, per row, for all rows in the range.

Formula

 =ARRAYFORMULA(SUMIF(IF(COLUMN(C2:E4),ROW(C2:E4)),ROW(C2:E4),C2:E4))

Screenshot

enter image description here

Example

I've created an example file for you: Sum over rows

Reference

https://stackoverflow.com/a/21804838/1536038

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Formula

Screenshot

Example

Reference

Related Solutions

Google-sheets – How to make a simple two column data chart in Google Spreadsheets

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic