I am trying to calculate the hours for each week (starting Monday).
What formula should I write to: retrieve the weekly sum of hours from another tab (based on week number)?
=sumif(C2:C, <your week number here>, D2:D)
One approach is to add all values, and subtract those already accounted for:
=if(A2="Sun", sum(D$2:D2) - max(E$1:E1), )
(To be entered in E2 and dragged down the column). This formula leaves the cell blank if A column does not have "Sun". Otherwise it adds up all D values up to and including the current row, but then subtracts what is already present in column E above.