Formula that uses column 7 row titles - DIFFICULT!!

[WhytheQ]

I have a feeling this is going to be a nasty formula!
I have the following table:

jack jill jon joan
red 1 1 1 1
blue 2 1 1 1
green 3 1 -1 -3
yellow 4 1 0 -3
pink 5 1 -1 -3
red 6 1 1 5
blue 7 1 -1 13
green 8 1 -1 21
yellow 9 1 0 29
pink 10 1 0 37
red 11 1 0 45
blue 12 1 -1 53
green 13 1 -1 9
yellow 14 1 0 5
pink 15 1 -1 4

i now need to be able to fill in the following table, that is in
another worksheet:

jack jill jon joan
red
blue
green
yellow
pink

....at present the cells in the second table are just made up of sums to
absolute references in the first table, therefore whenever rows or
columns get inserted into the first table things get messy with the
formulas!!

any help greatly appreciated.
Jason

 

[Max]

One way ..

Assuming source table is in sheet: X,
within A1:E16

and the summary table below is in sheet: Y,
within A1:E6

jack jill jon joan
red
blue
green
yellow
pink

In Y,

Put in B2: =SUMIF(X!$A:$A,$A2,X!B:B)
Copy across to E2, fill down to E6

 

[WhytheQ]

Thanks Max: seems to work fine.
If the column headers in both source and output tables were mixed up
would things be a lot more difficult?

Jason

 

[Max]

Thanks Max: seems to work fine.
If the column headers in both source and output tables were mixed up
would things be a lot more difficult?
Jason

In Y,

Put instead in B2:
=SUMIF(X!$A:$A,$A2,OFFSET(X!$A:$A,,MATCH(B$1,X!$1:$1,0)-1))
Copy across to E2, fill down to E6, as before