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)

[Villeroy]

With a single helper formula, a pivot table can do the job for each of your codes.
You may even keep the gaps in the code column if you check “Identify categories” in the pivot table options. With a code in every row, you are free to sort your table anyway you want without affecting the resulting pivot table.
ask130155.ods (59.8 KB)

[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)

[lodf2023]

Lots to think about. Thank you. I really wanted to replicate the formula down the column and just calculate the value on Saturday from the previous Sunday, without having to make sure the row references were updated. I wanted the data to control.


When I insert a row, sometimes formula references are updated and sometimes they are not, and then you spend your time looking for non-updated formulas. I think I know why now, but old habits are hard to break.


It is really helpful to download example spreadsheets and play around.


Thanks to all