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".

• Hi Jim, can't reproduce your figures. Shouldn't you be using SUMPRODUCT for COST and CUST1 to have the total per customer? – Jacob Jan Tuinstra Jul 15 '14 at 8:05

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))


## Example

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

## Reference

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

• Thank you so much - SUMPRODUCT is exactly what I was looking for (TQTY wasn't the sticking part, I can get that just by summing b2:e2, etc). – Jim Salter Jul 16 '14 at 0:59

The right function to use is SumProduct

Formula

=SUMPRODUCT(array1, [array2, ...])


Screenshot