Determine monthly volume from weekly figures where weeks cross over at month end

I've deleted your column G, so that all your dates are contiguous, and so the first full week of a month also has the part-total from the previous week added in, and the final week of a month shows the total just for that month. As the formula looks at both the previous week's commencing date and the date for the following week, you also need to include the date for the previous week at the start and the following week at the end of the date range, with zero calls. I've coloured the formula cell blue in the attached file to illustrate this, where this formula is in C3 and copied across to T3:

Formula:

=IF(MONTH(C1)=MONTH(D1),C2+IF(MONTH(C1)=MONTH(B1),0,(DAY(C1)-1)/7*B2),(DATE(YEAR(C1),MONTH(C1)+1,0)-C1+1)/7*C2)

Then, with a date of 1/10/2012 in D6, this formula in E6 gives the total for that month:

Formula:

=SUMPRODUCT(--(TEXT($C1:$T1,"yymm")=TEXT(D6,"yymm")),$C3:$T3)

and I've copied both cells across with a gap of a few cells, and then changed the dates in the first of those cells (formatted to show the month name only).

Hope this helps.

Pete