SUMIF Criteria Auto-Update on Copy/Paste

[SeventFloorProfessor]

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.

 

[Luke M]

=SUMIF('1st By Group'!B1:B135,A2,'1st By Group'!C1:C135)

 

[Luke M]

Since you're probably going to copy the formula down, should change to:

=SUMIF('1st By Group'!B$1:B$135,A2,'1st By Group'!C$1:C$135)

So that your arrays don't move.

 

[Jonathan Cooper]

I just tried the same thing and it worked fine. When you say "it won't let
me use this formula", can you elaborate on what happens?

On Sheet 1, I have A1="Name", A2="Doe, John" and B2 as =SUMIF('1st by
group'!$B$2:$B$135,TRIM(A2),'1st by group'!$C$2:$C$135)

On 1st by group, I have a1="group name", b1="student name" and c1=points.
starting on row 2 I have actual data.

Comments:
When you do this, you should use a dynamic range name for your data so that
your formula will continue to work as teachers add rows. Otherwise your
formula is dependent upon them 'inserting' a row.

If you are only going to have the sumif formula in B2, then the dollar signs
are not necessary, but if you plan to copy/paste, your going to need them.

lastly, if a someone puts a space after a students name, that will cause
problems as well. Doe, John is not the same as Doe, John_ (i.e., with a
space after the name). You might consider a using data validation on the
data input for student name...or a helper column to TRIM the name.

 

[SeventFloorProfessor]

What's weird is that I tried using the initial cell reference, and it kept
giving me an error message... but I found another solution "="&A2, and that
works. And then I tried your suggestion, and it works all of a sudden... I
don't know what happened.

Coincidentally, you anticipated my second question about copying (which I'd
already posted), so thanks!