Find totals by week

Question

I have a spreadsheet with various dates(a) (sequential) and pay(G). I would like to fill in the Week total column(h) with the total made for the week in the last week of the row. In the attached picture, I manually entered the values to show what I want to do.

Added table as requested

Date	Start	End	Subject	TimeSpent	Pay	WeekTotal
01/10/2024					533.42
01/12/2024					595.44	1128.86
01/15/2024					496.20
01/16/2024					248.10
01/16/2024					248.10	992.40
01/23/2024					471.39	471.39
01/29/2024					595.44
01/30/2024					285.32
01/31/2024					372.15
02/01/2024					372.15	1625.06
02/06/2024					372.15	372.15
02/12/2024					124.05
02/13/2024					372.15
02/14/2024					434.18	930.38

Can someone help with the formula?

Scott

Answer 1

For the older versions without Office 365, This will work in All versions:

=IF(WEEKNUM(A2)=WEEKNUM(A3),"",SUMPRODUCT($F$2:$F$15*(WEEKNUM(--$A$2:$A$15)=WEEKNUM(A2))))

OR

This is shorter, but it requires that the dates are sorted:

=IF(WEEKNUM(A2)=WEEKNUM(A3),"",SUM($F$2:F2)-SUM($G$1:G1))

Answer 2

Here is one way of doing this:

---

• Formula used in cell H2

=LET(
     _WeekN,WEEKNUM(+A2:A20),
     _WTotal, MMULT(N(_WeekN=TOROW(_WeekN)),G2:G20),
     IF(LOOKUP(_WeekN,_WeekN,ROW(A2:A20))=ROW(A2:A20),_WTotal,""))

---

This is the workaround for the total of 100 rows:

=LET(
     _Data, FILTER(A2:G100,A2:A100<>""),
     _WeekN,WEEKNUM(TAKE(_Data,,1)),
     _WTotal, MMULT(N(_WeekN=TOROW(_WeekN)),TAKE(_Data,,-1)),
     IF(LOOKUP(_WeekN,_WeekN,SEQUENCE(ROWS(_WeekN)))=SEQUENCE(ROWS(_WeekN)),_WTotal,""))

---