Sum weekly data into monthly periods per rule table

Trying to figure out simplest way to consolidate weekly data into monthly period dependent on ending week for period per rule table.  Please see the attached file. Example1.xlsx
acdecalAsked:
Who is Participating?
 
acdecalConnect With a Mentor Author Commented:
Glenn,
I couldn't use your solution because it required additional data to work.  I solved the problem by using the SUMIFS function.  See attached.
Thanks for your effort though.
Copy-of-Example1.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
This formula will calculate the sum of the weeks as defined in column C:
D11:  =SUM(OFFSET(OFFSET($G$2,0,MATCH(C10+1,$H$5:$EB$5,0)-1),0,1,1,MATCH(C11,$H$5:$EB$5,0)-MATCH(C10,$H$5:$EB$5,0)))

However, you will need to add columns for weeks 1-6 in order to produce a valid result for month 2 (weeks 7 & 8).  Same for weeks 32 -52

I've attached a modified version of your workbook.

Regards,
-Glenn
EE-Example1.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi,

Have you had a chance to review and or test my solution?  If so, and it works for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

Thanks,
 -Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
Good solution!  Glad you were able to figure it out.
-Glen
0
 
acdecalAuthor Commented:
Came up with better solution.
0
All Courses

From novice to tech pro — start learning today.