Read data in one table, then distribute and sum it in another table

Hello -

I'm not an Excel power user. I need a formula or VBA strategy to progressively go through departmental vacation entries in one table (start dates and end dates), and incrementally transfer and sum it by week and dept into another table in a different sheet. I have written VBA code to generate the 2 sheets contained in my simplified example file. Now I want to translate all the table entries in the "Data" sheet t to the table in the "Summary" sheet, organized by "Week Of" and "Department". Not sure if I need VBA or not. A big issue is that I'm not sure how to represent/account for the weeks between the start week and end week, since they are not explicitly given in the first table. It seems like I need to use loops, but that could get ugly and slow if I have to do it cell by cell for the table in the "Summary" sheet. Is there a more elegant approach, like using arrays? I'm not super familiar with arrays, so if that's a good choice a brief rationale for using them would be appreciated.

Ultimately I will read data from the resulting table to another workbook, where week by week dept headcount analysis takes place, so I want to represent all weeks between the first and last weeks in the table, even if they contain zeroes.

Thanks in advance for your help!

PWM