3

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

  • 1
    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
4

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

  • 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
0

The right function to use is SumProduct

Formula

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

Screenshot

Screenshot of using SUMPRODUCT function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.