Need assistance to correct a formula

You could use the Offset function.

You know that if the Row is a Sunday, you want the sum of hours for that row and the 6 rows before it. 

=SUM(OFFSET(C12,0,0,-7,1))

In the example above C12 is the cell of interest that falls on a Sunday, it is the starting point of our Sum range and so 0,0 means we start 0 rows and 0 columns away from it, we want that row and the 6 rows before it so our height is -7 and we only want that column so the width is 1.

(If for example you had columns for AM hours and PM hours and then wanted to sum them all then the width would be 2)

It could also be written (C12,-7,0,7,1) or even (A12,0,2,-7,1) and get the same result. 

Next embed into an If statement so that it only comes into effect if the date is a Sunday.  Similar to as you have done already but we can just use the date value from the A column for the calculation:

=IF(WEEKDAY(A12,2)=7,SUM(OFFSET(C12,0,0,-7,1)),””)

The second argument in the Weekday function “2” establishes Monday as 1 and Sunday as 7.

The only issue with this solution is that if there are not 7 rows prior for the offset to sum then it will return #REF! error.

The only way I can think to mitigate is to use an IFERROR and CELL to get the row number of the cell to use in the calculation instead:

=IF(WEEKDAY(A2,2)=7,IFERROR(SUM(OFFSET(C2,0,0,-7,1)),SUM(OFFSET(C2,0,0,CELL(”row”,C2)*-1,1))),””)

The CELL(”row”,C2) function returns the row number of the cell referenced in the 2nd argument. In your example the Sunday that doesn’t have a full week before it is Row 5, and as our height is for the rows before and not after we multiple by -1 to make it a negative number.

Btw, in the original example, if the Jan dates of column A correspond to 2026 then the Days of the week of column B are out a day.