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

## Screenshot ## 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). Jul 16 '14 at 0:59

The right function to use is SumProduct

Formula

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

Screenshot 