Weekly data into monthly data inc. splitting weeks that overlap months

Hi all,

I have a data set that contains a number of week ends i.e. week ending 03/09/23, 10/09/23 etc. and I need to summarise this into a monthly summary. Where the week ending is over two months i.e. 03/09/23 I want it to take the total and divide by 7 and multiply it by the number of days in the month i.e. for 03/09/23 I would want 3 days worth in Sept and 4 days in Aug. Then on top of this I need it to look up categories and bring it back based on those as well as the dates.

Example sheet attached.

The weekly forecast has a helper row in it (Row 10) which identifies the number of days in the month (this may not be needed). I need the formula in the Summary tab cells D13:O26 and D31:O58.

I have added a formula (D13) that incorporates the formula in cell E13 with another formula that attempts
to apportion any amounts in the week endings that go over two months but this doesn't work...I don't know why? I have added notes onto the file which may help explain.

Can someone either help amend the formula in cell D13 to work or provide another formula that works please?

Thanks in advance.

Why have you started a new thread? You are only allowed one thread per topic on this forum, to avoid duplications.

Pete