Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

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
0
acdecal
Asked:
acdecal
  • 3
  • 2
1 Solution
 
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
 
acdecalAuthor 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:
Good solution!  Glad you were able to figure it out.
-Glen
0
 
acdecalAuthor Commented:
Came up with better solution.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now