Apply SUM formula to a range of cells showing the total every nth cell-CodePudding

I have data as follows in my sheet:

DATE        DAY KM      SPD         MON/TOT TOTAL DIST.
                    
2022.08.21  SUN 8.47 km 8.00 km/h   AUG--5  42.79 km
2022.08.22  MON 8.62 km 7.90 km/h       
2022.08.23  TUE 8.50 km 7.79 km/h       
2022.08.25  THU 8.61 km 8.05 km/h       
2022.08.28  SUN 8.59 km 8.39 km/h       
2022.09.01  THU 9.10 km 8.25 km/h   SEP--2  10.10 km
2022.09.01  THU 1.00 km 9.90 km/h       
2022.10.01  SAT 9.60 km 8.00 km/h   OCT--4  26.30 km
2022.10.01  SAT 2.00 km 8.00 km/h       
2022.10.05  WED 5.00 km 8.70 km/h       
2022.10.05  WED 9.70 km 6.00 km/h       
2022.11.01  TUE 9.90 km 8.00 km/h   NOV--1  9.90 km

I cannot figure out how to apply the formula (formula in Total Dist.) to the whole range as SUM does not work in ARRAYFORMULA, I have tried MMULT and SUMPRODUCT but was not successful.

It might be a bit tough to understand so please take a look at the sheet.

https://docs.google.com/spreadsheets/d/1AuJ6BnVkgYRxwxstAz-iHKVX5XUbIaS-cQjr2xDLJr8/edit?usp=sharing

Basically I never know how many times the activity will be done each month so I need a way to SUM the Total Distance and Total Speed through an ARRAYFORMULA so that the sheet does not get broken if extra rows are inserted. In the example above I entered the following formula in each cell manually but that is not feasible, the two formulas used in the "Mon/Tot" and "Total Dist." are

=ARRAYFORMULA(IF(B4:B="",,(IF(I4:I=J4:J,,UPPER(TEXT(B4:B,"MMM"))&IF(I4:I=J4:J,,"--")&IF(I4:I=J4:J,,COUNTIFS(B4:B,">="&B4:B, B4:B,"<="&EOMONTH(B4:B,0))
)))))

The above formula uses helper columns and tells you the Month (makes it easier to read the totals for the sheet users) and gives a count of how many times the activity has been done for the said month

=SUM(INDIRECT("D"&ROW()&":D"&ROW() INDEX(SPLIT(INDIRECT("F"&ROW()),"--"),1,2)-1))

The above formula sums the Total Distance in the said month, dynamically as the sum range always changes depending on how many times the activity has been done in a said month --> this is the formula I am trying to apply to the whole column through the use of an ARRAYFORMULA or the equivalent but have so far not been successful.

Also, any advice on any of the other formulas is welcome as I am not sure as to them being the best way to achieve what they are achieving.

CodePudding user response：

ArrayFormula(IF(F4:F="",,VLOOKUP(MONTH(LEFT(F4:F,3)&" 1")-1,QUERY(B4:D,"select month(B),sum(D) group by month(B)"),2,FALSE)))

Calculate the sum of each month by QUERY, group the 0-based month values
Formulate the 0-based month value from Column F with MONTH
Do a VLOOKUP with the month value from 2 as key and table from 1