I am designing an excel file for my school system. The goal is to allow
teachers to input a class roster in a row, then have other sheets designed
for specific activities that generate points. The other sheets have the
student names in varying orders, depending on how they need to be arranged
for each activity. The first sheet tallies the points, providing a grand
total.

I am trying to use the SUMIF function on sheet 1. The problem is this:

If A2 has "Doe, John" in it, and B2 contains my SUMIF Function, it won't let
me use this formula: =SUMIF('1st By Group'!B1:B135,"Doe, John",'1st By
Group'!C1:C135), where '1st By Group' B1:B135 is a list of groups that
contains multiple instances of a student's name (as they are in multiple
groups), and '1st By Group' C1:C135 contains the points they earned within
that group.

I would like to replace the criteria ("Doe, John") with "A2", so that when I
(or other teachers) get a new class roster each semester or when a teacher
other that myself uses it for their unique class, we can simply input the new
roster and create new groups, and the formula will automatically update to
SUM the points for the new instances of the student's names.

I'm sorry if there's a better way to explain this or if this is ridiculously
simple. I teach the College & Honors English program, and I'm a technology
nerd, so I get assigned to this.