Google Sheets – Sum Total from Multiple Sheets for a Specific Name

This formula will provide the information:

=QUERY({Arnold!$A$3:$F;Barrett!$A$3:$F;Beeler!$A$3:$F;Bernal!$A$3:$F;Bland!$A$3:$F;Cameron!$A$3:$F;Bertuccelli!$A$3:$F;Cheng!$A$3:$F;A_Cheng!$A$3:$F;Cirigliano!$A$3:$F;Haut!$A$3:$F;Holloway!$A$3:$F;Kuhre_HS!$A$3:$F;Kuhre_MS!$A$3:$F;Mentesh!$A$3:$F;Porter!$A$3:$F;Reynolds!$A$3:$F;Rocha!$A$3:$F;Taylor!$A$3:$F;Turner!$A$3:$F;Van_Luevan!$A$3:$F;Weed!$A$3:$F;Youngblood!$A$3:$F},"Select Sum(Col6) where Col1='"&$A3&"'label sum(Col6) ''")

One of the challenges of the solution is that student names are not in contiguous rows on each sheet. The "Bertuccelli" sheet is a perfect example where there are gaps of several rows between several of the student names. Consequently, I thought it best to create a "master list" of student names. This then becomes the name that is matched to the names on each Teacher sheet.

This screenshot shows the results of the formula, and the Master List of student names.

Logic

The Sheet for each Teacher is referenced for the range $A3:$F. This ensures that any students added to a sheet will be automatically included in the results.

The Select statement is: "Select Sum(Col6) where Col1='"&$A3&"'label sum(Col6) ''". Let's break this down.
Select Sum(Col6) - this sums Col6 (=Column F "Total Absences").
where Col1='"&$A3&"' - this compares the name in Col1 (Column A-Student Name) to the student name on the master list.
The overall effect is to Sum "Total Absences" for each Teacher where the Student Name equals the reference name.

SPREADSHEET CHANGES REQUIRED

There should be no spaces in any sheet name. Several sheets were renamed.
A Cheng -> A_Cheng
Kuhre HS - > Kuhre_HS
Kuhre MS - > Kuhre_MS
Van Luevan -> Van_Luevan

INTEGRATION

How this formula will be integrated into the questioner's model is unclear.
I suggest a VLOOKUP on each Teacher sheet:

=IFERROR(vlookup(A3,'Student List'!A$3:B$15,2,false),"")

I do agree that it is worthwhile that each teacher should know the total absences for each student, in addition to the absences for their own class. However, it would seem important that the school administration should know the absences by teacher. I prepared this sheet to prove the results of the formula, but is seems to me that it might also be a useful tool for administration.

Limitations

It's desirable that both formulas should be an ARRAYFORMULA. I've decided to leave that to someone more clever than me; I hope they post their solution here.