Count in specific ways in Google sheets

Question

I am working on getting some specific counts but cannot figure out how to do it.

I have the following sheet:

Duration for subject 1 is 30 min and 90 min for subject 3

Subject 1	Subject 1	Subject 1	Subject 1	Subject 1	Subject 1	Subject 1
Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Day	Day	Day	Day	Day	Day	Day
Bob						Jules
Night	Night	Night	Night	Night	Night	Night
	Marc		Dylan
	Yan
	Stephen
Subject 3	Subject 3	Subject 3	Subject 3	Subject 3	Subject 3	Subject 3
Day	Day	Day	Day	Day	Day	Day
	Ian	Rene
		Steff
		Bob
Night	Night	Night	Night	Night	Night	Night
		Jack
				Daniel

This goes on with more subjects I want it to be dynamic so adding/removing rows would not ruin the calculation

My goal is to obtain the following automation result in another tab

Total Subject 1

Total	DAY	NIGHT	Total DURATION (MN)
Monday	1	0	30
Tuesday	0	3	90
Wednesday	0	0	0
Thursday	0	1	30
Friday	0	0	0
Saturday	0	0	0
Sunday	1	0	30
TOTAL	2	4	180

Total subject 3

Total	DAY	NIGHT	Total DURATION (MN)
Monday	0	0	0
Tuesday	1	0	90
Wednesday	3	1	360
Thursday	0	0	0
Friday	0	1	90
Saturday	0	0	0
Sunday	0	0	30
TOTAL	4	2	540

I made a test sheet that works, but ideally I want the result to search for Subject 1 and subject 3 no matter where it is in the sheet and count how many non empty cells are between Day and Night for every day for example. Also the tab name could change all formulas at once for example. I tried =COUNTA(INDIRECT("'"&$E$1&"'!C4:C9")) instead of =counta(Sheet1!C4:C9) but it is not dynamic for the range anymore. Does it make sense and can somebody help?

Link to test sheet

Answer 1

Here's a generalized approach which you may adapt accordingly:

=let(Σ,tocol(,1),Λ,Sheet1!A:A, reduce(Σ,A3:index(Λ,match(,0/(Λ<>""))),lambda(a,c,ifna(vstack(a,if(c<>"Day",Σ,vstack(hstack(offset(c,-1,),index(N:N,row(c))),hstack("Total","DAY","NIGHT","Total DURATION"),let(
 x_,offset(c,,,+filter(row(Λ),Λ="Night",row(Λ)>row(c))-row(c),13), x,reduce(Σ,x_,lambda(f,q,vstack(f,if(q<>"Day",Σ,hstack(index(A1:M1,column(q)),counta(offset(q,1,,rows(x_)-1))))))),
 y__,+filter(row(Λ),Λ="Night",row(Λ)>row(c))-row(c), y_,offset(c,y__,):offset(c,let(Σ,+filter(row(Λ),Λ="Day",row(Λ)>row(c)),if(isna(Σ),rows(Λ),y__-2+Σ-+filter(row(Λ),Λ="Night",row(Λ)>row(c)))),,,13),
 y,reduce(Σ,y_,lambda(f,q,vstack(f,if(q<>"Night",Σ,counta(offset(q,1,,rows(y_)-1)))))), z_,index((choosecols(x,2)+y)*index(N:N,row(c))),z,hstack(x,y,z_),vstack(z,hstack("TOTAL",bycol(choosecols(z,2,3,4),lambda(Σ,sum(Σ)))))),)))))))