We have an annual and monthly water-consumption data table, and the year is filled in only on the left of the first month of each year. Part of the data is shown as below:

The task is to calculate the total water consumption of each year. To achieve this, enter the formula in cell D1:

=spl("=E(?1).derive(:TotalWater).group@i(Year!=null).run(~(1).TotalWater=~.sum(Water)).conj().new(TotalWater)",A1:C20)

When meeting a non-null value in the column Year, create a new group. Option @i means creating a new group when the condition is satisfied. Assign the sum(Water) of the current group to the first row of each group, and create a new data set and take out the TotalWater.
 

esProc Desktop and Excel Processing
11.9 Group when meeting blank row
11.11 Group by interval of data values