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

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

On your question would there be a maximum limit on the entries after the night row? or What is the other pattern or marking that you can use to mark the end of that data cluster. — Babanana
– Babanana, Commented Jan 23 at 9:13
@Babanana The columns will only include the name of the subject, Day, Nights and the names in between. The days of the weeks are actually higher up in the sheet like i wrote in the test sheet — Jerome
– Jerome, Commented Jan 23 at 9:38
Is the data originally collected in that sheet or is the sheet generated from other data? The format of the sheet is not really suitable for a proper counting like you want it - so a different structure of the sheet would help — cyberbrain
– cyberbrain, Commented Jan 23 at 9:52

rockinfreakshow · Accepted Answer · 2025-01-23 11:21:14Z

0

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(Σ)))))),)))))))

answered Jan 23 at 11:21

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerome Jan 24 at 11:54

That looks great. Would it be possible to have an extra column counting only Day and one counting only Night?

Collectives™ on Stack Overflow

Count in specific ways in Google sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related