SUMPRODUCT of columns based on matrix position

**jungpil** · 03-20-2009, 03:52 AM

Thanks in advance for any hints, pointers and/or suggestions. This is driving me crazy.

I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.

For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.

I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)

Please Login or Register  to view this content.

But I keep getting a #VALUE! error.

What really drives me crazy is that if I do something like:

Please Login or Register  to view this content.

, that works perfectly fine (only for the diagonals) but I'm still at a loss for off-diagonal elements.

Any help will be greatly appreciated. Thanks!

**DonkeyOte** · 03-20-2009, 04:32 AM

For I16 you say this equates to 8,3 (or 3,8 using RC) and then "sumproduct I4:I11, D4:D11" rather than I2:I11 / D4:D11 so this would imply that the 3 denotes both starting row and 2nd column in the matrix ?
If this is the case the fixed offset of 10 rows in your existing formula is incorrect. Can you clarify ?

If it should be I2:I11 / D2:D11 (ie always 10 rows) then:

A14:
=SUMPRODUCT(INDEX($B$2:$K$11,0,COLUMNS($B14:B14)),INDEX($B$2:$K$11,0,ROWS(B$14:B14)))
copied across Matrix

Let us know.

**DonkeyOte** · 03-20-2009, 04:57 AM

I should have added a "what if it is D4:D11" solution... ie range shrinks as you progress... using your OFFSET approach (volatile) something along the lines of the below perhaps:

Please Login or Register  to view this content.

**jungpil** · 03-20-2009, 09:40 AM

Originally Posted by DonkeyOte

For I16 you say this equates to 8,3 (or 3,8 using RC) and then "sumproduct I4:I11, D4:D11" rather than I2:I11 / D4:D11 so this would imply that the 3 denotes both starting row and 2nd column in the matrix ?
If this is the case the fixed offset of 10 rows in your existing formula is incorrect. Can you clarify ?

If it should be I2:I11 / D2:D11 (ie always 10 rows) then:

A14:
=SUMPRODUCT(INDEX($B$2:$K$11,0,COLUMNS($B14:B14)),INDEX($B$2:$K$11,0,ROWS(B$14:B14)))
copied across Matrix

Let us know.

Actually, you are right, it should always be 10 rows so I2:I11 / D2:D11 is correct. Also, the formula works fine. Thanks a lot!

**jungpil** · 03-20-2009, 09:46 AM

I have a quick follow-up question...

Why didn't my original formula work? My guess is that OFFSET returns a reference whereas SUMPRODUCT requires an array (or a list of arrays) as its argument(s). And since SUMSQ has as inputs a list of numbers, the reference produced by the OFFSET worked, since basically a reference is a list of cells(, but not necessarily an array).

Am I on the right track? Thanks.

**DonkeyOte** · 03-20-2009, 10:05 AM

Issues with:

=SUMPRODUCT(OFFSET($A$1,1,COLUMN(B2)-COLUMN($A$1),10,1),OFFSET($A$1,ROW(B2)-ROW($A$1),10,1))

1 - you are missing a ROW offset position in the 2nd OFFSET
2 - you should use COLUMNS($B1:B1) and ROWS(B$2:B2) rather than COLUMN()-COLUMN() and ROW()-ROW()

eg:

B14:
=SUMPRODUCT(OFFSET($A$1,1,COLUMNS($B2:B2),10,1),OFFSET($A$1,1,ROWS(B$2:B2),10,1))

The advantage of INDEX approach is that it is not Volatile unlike OFFSET.