Excel Table of Daily hours summarized by week number for a user

0
votes

I'm having a hard time getting my head around what I think is a simple enough problem.

I have an Excel table of hours by day for each user i.e.:

       Date1, Date1+1, Date1+2, Date1+3,... Date1+n
User1   8      8         4       6      ...   2       
User2   5      2         8       3      ...   7
User3   0      7         5       0      ...   8

For forecasting purposes this grid looks several months into the future.

I do my work daily, others want it by week. I'd like to automatically generate the same table of data but rolled up by WeekNum.

I tried setting a year-weeknum at the top of the daily table and then using a SumIfs function to compare the user name and week num to sum up the daily hours in another tab for weekly data but I just couldn't get it to function properly.

=SUMIFS('Act - Forecast Hours'!$G$6:$AAL$35,'Act - Forecast Hours'!$A26,$A25,'Act - Forecast Hours'!S$4,O$3)

I think I'm overcomplicating a solution, any help is appreciated.

TIA Rob

1
excelexcel-formulasumifs

1 Answers

0
votes

OK, I may have come up with an approach.

Since on my main Hourly Sheet the format is fixed, i.e. each week is 7 days and increments.

I setup a second sheet where I called a vertical and a horizontal offset and used the following formula:

=SUM(OFFSET('Act - Forecast Hours'!$G$9,$A5,D$2,1,7))

$A5 and D$2 refer to offset counts that increment by 7. As you copy the formula to each cell it increments the Row / Column to point to the right spot. Then for the Height and Width I look at a grid 1 row high and 7 wide to select each day of the week.

It works, I'm happy. I'm certainly interested in a more refined approach if there is one :-)

Thank You to anyone that does read through the question!

Regards Rob