Weekly sums. Was: Sum values from the current row backwards until string is found

[lodf2023]

Every Saturday I want to sum values from the current row backwards to the previous sunday or 7 rows back. The by row method has more flexibility and I don’t have to worry that I have the correct absolute rows. Is there another way besides using indirect.

I’d also like to sum backwards until a certain string. What functions should I look at?

Should I be wrapping, at least the by row, with error handling for boundary cases (i.e., top of data and less than 7 rows or no string match)?

# By Absolute
=IF(WEEKDAY(A17)=7,SUM($B11:$B17),"")

# By Row
=IF(WEEKDAY(A17)=7,SUM(INDIRECT("B" & ROW() & ":B" & (ROW()-6))),"")

A               B   C
2025-10-31, Fri	18
2025-11-01, Sat	17  35
2025-11-02, Sun	16
2025-11-03, Mon	14
2025-11-04, Tue	10
2025-11-05, Wed	15
2025-11-06, Thu	12
2025-11-07, Fri	15
2025-11-08, Sat	15  97
2025-11-09, Sun	14

[PKG]

@lodf2023

What functions should I look at?

Data → Pivot Table

Untitled2.ods (28,8 KB)

[lodf2023]

My bad, I would like the summation inline with the rest of the data, but pivot tables sure are powerful.


Example:

2025-01-01, Wed 121	
2025-01-02, Thu 110	
2025-01-03, Fri 84	
2025-01-04, Sat 54 369
2025-01-05, Sun	140	
2025-01-06, Mon	58	
2025-01-07, Tue	134	
2025-01-08, Wed	82	
2025-01-09, Thu	117	
2025-01-10, Fri	50	
2025-01-11, Sat	88	669
2025-01-12, Sun	63	
2025-01-13, Mon	92	
2025-01-14, Tue	79	
2025-01-15, Wed	132	
2025-01-16, Thu	117	
2025-01-17, Fri	136	
2025-01-18, Sat	79	698
...

[PKG]

I would insert a column with a code and calculate my totals based on this code.

[lodf2023]

Ok…cool…What is the significance of the second part of the or in the second image? Is it suppose to catch the boundary case - top of the data? I got some weird results if I add in the dates at the top because they are part of week 1 of the new year.

2024-12-29
2024-12-30
2024-12-31

[PKG]

@lodf2023

What is the significance of the second part of the or

=EOMONTH($B$2,11) → YEAR-12-31

EOMONTH(StartDate; Months)

[fpy]

@lodf2023

another

the good old fashion way with a cumulative column (and conditional format):

[Lupp]

My two cents - of course without an image, but with an example file:
disask_130155_WeeklySum.ods (30.7 KB)