How to 'restart' a sumif formula within an Arrayformula in google sheets-CodePudding

I'd like to achieve the output below. Everything is manually added, except the starting time (and Day Nr). This should be calculated with the use of Arrayformula. It's working for 1 day, but now i'd like to add more days (manually). The duration should sum the day start durations for only the specific day. When a new day row is inserted, it should start over new.

I'm using this code now, but only working with the starting time of Day 1 (the first row).

CodePudding user response：

You can use the new Scan function to reset the addition of times:

=ArrayFormula(if(C3:C<>"Clip",,
vlookup(row(C3:C),filter({row(C3:C),E3:E},C3:C="Day"),2,true) 
scan(0,if(C3:C="Day",-1,H2:H),lambda(a,c,if(c<0,0,a c)))))

In your locale it should be:

=ArrayFormula(if(C3:C<>"Clip";;
vlookup(row(C3:C);filter({row(C3:C)\E3:E};C3:C="Day");2;true) 
scan(0;if(C3:C="Day";-1;H2:H);lambda(a;c;if(c<0;0;a c)))))