# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel 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
Excel 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
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.
Copy-of-Example1.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Excel VBA DeveloperCommented:
Good solution!  Glad you were able to figure it out.
-Glen
0
Author Commented:
Came up with better solution.
0