possible ARRAY formula !?

[WhytheQ]

I have a table like the below

01-Jun-06 02-Jun-06 03-Jun-06
04-Jun-06..........01-Jul-06....
PlayerA 10 10 5 50
PlayerB 20 20 6 40
PlayerC 30 30 9 609
PlayerD 40 40 40 21


the table goes the full width of the worksheet i.e upto column IV
I have another table which sums the players scores by month:


Jun-06 Jul-06 Aug-06 Sep-06.........Dec-06....
PlayerA
PlayerB
PlayerC
PlayerD


is there a formula I can use which will sum all the daily figures from
the first table i.e in the second table PlayerA/Jun-06 there should be
the total of player A's daily June figures.

Any help greatly appreciated.
Jason

 

[Max]

One way using SUMPRODUCT (non-array)

Assume source table in Sheet1, real dates in B1 across, players listed
in A2 down

Assume the summary table below is in another sheet, with "1st of month"
real dates in B1 across, players listed in A2 down, with the listing of
players not necessarily in the same order as in Sheet1:

Jun-06 Jul-06 Aug-06 Sep-06.........Dec-06....
PlayerC
PlayerA

....

Put in B2 (normal ENTER will do):
=SUMPRODUCT(--(TEXT(Sheet1!$B$1:$IV$1,"mmm-yyyy")=TEXT(B$1,"mmm-yyyy")),OFFSET(Sheet1!$B$1:$IV$1,MATCH($A2,Sheet1!$A:$A,0)-1,))

Copy B2 across and fill down as far as required to populate the summary
table