I humbly seek your help.

I'm working on tabulating a bit o' data for a project at my school.

Spreadsheet Info I have a spreadsheet where teachers have to click their name (which is linked to their own sheet). This brings the teachers to a sheet that contains the roster of their homeroom students. The teachers need to tick a box in columns B, C, and D to indicate if the students brought their charged chromebook, their charger, and their sleeve. They left the box unticked if the student did not complete this task. This spreadsheet contains 70 teacher's names and 1800 student names.

What I'm seeking help with The teachers have completed their task- and I'm trying to tabulate the # of unchecked boxes for each column (B,C,D) per teacher (or, per sheet). I know how to sum the unchecked boxes. What I need help with is trying to find a way to apply a formula across 70 sheets w/ unique names (without manually typing each unique name in a formula). My limited formula knowledge results in being unable to copy down (or across) formulas when the sheet name is referenced via link in a column.

Simplified Example Sheet: I have a simplified example linked below.

CodePudding user response：

You were probably right with COUNTIF and Indirect. Possibly you struggled with single apostrophes for the name of sheets with spaces. You can try this in B2:

=MAKEARRAY(COUNTA(A2:A),3,LAMBDA(r,c,COUNTIF(INDEX(INDIRECT("'"&INDEX(A2:A,r)&"'!B2:D"),,c),FALSE)))