Sum Data Based on Weekday and Weeknum

Hi All,

I need some help with the best way to do this. I've attached the spreadsheet.

I want to display by data in the format in columns G through O. It is currently in the format of columns A through E.

Is there a formula that if A4 is week 26 of 2006 and a monday set cell I4 equal to D4. If A5 is week 26 of 2006 and a tuesday set cell J4 equal to D5. If A6 is week 26 of 2006 and a wednesday set cell K4 equal to D6, etc. Eventually I will have a few years worth of data in the A through E columns.

I tried using the sumproduct, but I keep getting the #value error. I tried inserting two columns after column A and calculating the weeknum and weekday values of A4, but I still can't get the right value in the G through O columns.

Any suggestions is appreciated

Hi NBVC,

Thanks for the response, but this isn't what I'm looking for. Your answer puts the result in the wrong rows. The 7/3/06 number ends up in cell j6. It should be in j5. Then 7/4/06 is in K7 and it should be in k5. 7/5/06 is in L8, not L5, etc.

I'm wondering if I add a couple of columns between Column A and B, then in the new B and C columns, use the weekday and weeknum functions for the date in column A, could I just say if B4 =monday and if C4 = H4 set cell I4=D4.

I don't have to sum any numbers, their will only be one match for each cell in the G to O group of columns. All I'm trying to do is rearrange the data from vertical to horizontal. Each row in the G to O columns represents one week. The date in column G is the first day of each week of the year.

Any thoughts

NBVC,

I think I have it. Looking at your suggestion and realizing I don't have to sum the numbers. I adjusted your suggestion, added the new columns, and changed it to equal to. Now it works.

Cell K4 has the formula [=SUMPRODUCT((($C$4:$C$100)=$J4)*(($B$4:$B$100)=1)*($F$4:$F$100))]

Thanks for the suggestion.