SUMPRODUCT formula help??

[GoBucks]

I am looking for a formula which I believe may be accomplished with
SUMPRODUCT. My first worksheet (sheet1) is a capacity planning report by
consultant. There are %'s assigned for for each work week that represent
projected time allocated for a specific project. The data below are Number
values rather than %'s. The work week dates are every other Monday.

sheet1
row/col A B C C E F G
5 Consultant 4/6 4/13 4/20 4/27 5/4 5/11
6 Name 1 100 100 100 50 10 X
7 Name 2 50 50 50 50 100 X


On sheet2 I am looking to summarize this weekly data into a monthly summary
format. I am looking at converting the projected weekly time allocation %'s
in sheet1 into monthly projected work hours. This is assuming 40 hr work
weeks.

sheet2
Consultant Apr-09 May-09
Name 1 ? hrs
Name 2 ? hrs

Can this be done? Any help would be appreciated. I can't seem figure this
one out.

 

[Bob Phillips]

Try this array formula

=SUM(IF(TEXT(Sheet1!$B$1:$G$1,"mmyyyy")=TEXT(B$1,"mmyyyy"),Sheet1!$B2:$G2))
/SUM(IF(TEXT(Sheet1!$B$1:$G$1,"mmyyyy")=TEXT(B$1,"mmyyyy"),Sheet1!$B$2:$G$3))*40