I am stuck in terms of how to set a formula to get the sum total for a specific "name" from multiple sheets.

  • Hi. This question is starting to age, and it's because there's not enough information to answer it. The screenshot is somewhat helpful but it's really not clear where the totals will go, and what fields they are summing. Would expand on your question so that we have a better understanding rather than trying to guess. Would you also please share your spreadsheet; there's no substitute for being able to see the exact layout and number of sheets and fields. And it would help to how you have already tried to resolve this problem. What did you try; how close did you get; why didn't it work. Thanks. – Tedinoz Oct 13 '18 at 4:34
  • Hi Tedinoz . Thanks for the reply. I am trying to find out how to add up the number of absences of a particular student in all the tabs in the same row. In this case, each teacher in each tab would not only see the particular student's absences in their class but in all the student's classes also. There would be 2 columns for "absences": one for their class and one for all classes. For the Tab titled "Cheng," you will see two coloumns. – Vineyard Teacher Oct 14 '18 at 21:42
  • Here is the link. drive.google.com/… – Vineyard Teacher Oct 14 '18 at 21:42

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.
enter image description here


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.


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


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.

Analysis by student by Teacher


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.