Help changing weekly figures to monthly

[Totot]

Hi, hope this is the right part of the forum for this query - and hope someone can help.

I am trying to create a sheet/formula that will convert weekly sales figures into monthly figures. With split weeks being split pro-rata to the number of days (assuming 7 in a week, not weekdays).

A simplified version of my excel sheet - which has 52 columns for week commencing dates.

Row 3 (week commencing)
01/04/2012 08/04/2012 15/04/2012 22/04/2012 29/04/2012 06/05/2012 etc

Row 4 (weekly sales target)
£54000 £22000 £29000 £30000 £32000 etc

I would like a way of ending up with total sales per month taking into account the split weeks. I'd originally expected to need to calculate this over 12 new rows lower on the spreadsheet (for Apr – Mar) with a total on the far right of the sheet but cannot work out a forumla that works.

Any help would be much appreciated - Thanks

[Ace_XL]

Please upload a sample worksheet. Would be much easier to resolve.

[Totot]

Please upload a sample worksheet. Would be much easier to resolve.

Thanks
Attached is a simple version of my sheet, which I hope explains it better

Excel help2.xlsx

[kvsrinivasamurthy]

File with Formulas is Attached.
Any clarifications welcome.
Helper rows 8 & 9 are used.
I hope this is ok.
If satisfied mark the thread SOLVED
Thanks.

[Totot]

Great - that seems to work as required.

Thanks for your help.

[oeldere]

Or with an pivot table (see my example in sheet 2).

Do you have questions, just ask.