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

Question

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). Code below thanks to doubleunary

=arrayformula({"START"; 
   if(C3:C="DAY";E3:E;  
      if( 
      isnumber(H3:H); 
      vlookup("DAY";C3:E;3;true) + sumif(row(H3:H); "<=" & row(H3:H); H2:H); 
iferror(1/0) 
))
})

This is a link to the google doc

Type (manual input)	Day Start (manual input)	Day Nr (calculated)	Starting Time (calculated)	Duration (input manual)
Day	08:00	1		------------
Clip		1	8:00	1:20
Clip		1	9:20	1:00
Clip		1	10:20	0:20
Clip		1	and so on...	and so on...
Day	7:30	2
Clip		2	7:30	2:00
Clip		2	9:30	1:40
Clip		2	11:10	0:20
Clip		2	and so on...	and so on...

It looks like you copied this from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, you can [edit], include a [link](/editing-help#links) to the source, mention the author's name, and [quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Oct 10 '22 at 06:53
I've updated the post with the link to the source and author's name. — Marcel Buunk, Oct 10 '22 at 10:05

player0 · Answer 1 · 2022-10-10T15:28:48.593

1

try:

=ARRAYFORMULA(IF(A2:A="",,IF(B2:B<>"",,IF(ISTEXT(E2:E),,{0;
 TEXT(VLOOKUP(ROW(B2:B), IF(B2:B<>"", {ROW(B2:B), B2:B}), 2, 1)+
 SUMIF(ROW(E2:E), "<="&ROW(E2:E), E2:E)-SUMIF(C2:C, "<"&C2:C, E2:E), "[h]:mm")}))))

=ARRAYFORMULA(IF(C3:C="";;IF(E3:E<>"";;IF(ISTEXT(E3:E);;{0;
 TEXT(VLOOKUP(ROW(E3:E); IF(E3:E<>""; {ROW(E3:E)\ E3:E}); 2; 1)+
 SUMIF(ROW(H3:H); "<="&ROW(H3:H); H3:H)-SUMIF(E3:E; "<"&E3:E; H3:H); "[h]:mm")}))))

edited Oct 10 '22 at 15:28

answered Oct 10 '22 at 11:27

player0

124,011
12
67
124

I'm getting a VLOOKUP out of range message. I've added a second tab (sheet) with your solution, maybe you see something I'm doing wrong. – Marcel Buunk Oct 10 '22 at 15:03
@MarcelBuunk use 2nd formula. see: https://stackoverflow.com/a/73767720/5632629 – player0 Oct 10 '22 at 15:29

Tom Sharpe · Accepted Answer · 2022-10-10T15:21:21.657

1

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

edited Oct 10 '22 at 15:21

answered Oct 10 '22 at 11:59

Tom Sharpe

30,727
5
24
37

I get the following message. Is there something I'm doing wrong? Error The size of the range for FILTER is incorrect. Expected number of rows: 30, expected number of columns: 1. Current number of rows: 15, current number of columns: 1. – Marcel Buunk Oct 10 '22 at 14:41
I've added a second tab (sheet) with your solution, maybe you see something I'm doing wrong. – Marcel Buunk Oct 10 '22 at 15:02
In the curly brackets, the ; becomes a \ in your locale. Will add to my answer. – Tom Sharpe Oct 10 '22 at 15:20

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

2 Answers2