Maarten,

Use this formula in B8

=IF($A8B$7,SUMPRODUCT(OFFSET($A$1,MATCH($A8,$A$2: $A$5,0),1,1,5),OFFSET($A$1
,MATCH(B$7,$A$2:$A$5,0),1,1,5)),"")

and copy down and across.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maarten" wrote in message
...
Dear list,

I've got a table that looks like this (numbers on left side are row
numbers):
1 S1 S2 S3 S4 S5
2 X1 1 0 1 1 1
3 X2 0 0 0 1 1
4 X3 0 1 0 0 0
5 X4 1 1 0 0 1
For each cell in the next table (symmetric below and above diagonal) I
want
the sumproduct of two rows Xi and Xj. Cell X2-X1 should contain the
sumproduct of row X1 and X2.

6
7 X1 X2 X3 X4
8 X1
9 X2 2
10 X3 0 0
11 X4 2 1 1

I tried to solve this by using the following formula:
= SUMPRODUCT(OFFSET($B$2:$F$2;COLUMN()-2;0;1;6);
OFFSET($B$2:$F$2;ROW()-8;0;1;6))
However, this results in zeros in each cell. The help file mentions that
SUMPRODUCT considers non-numerical values as zeros.
Can someone help me with this?

Maarten