On Feb 6, 11:29 pm, JMB wrote:
To throw out some other suggestions that appeared to work okay (if I
understand what you're asking):

To sum all values in row 5 up to (and including) the midterm, you could try:
=Sum(Sheet1!A5:Index(A5:Z5, Match("m", Sheet1!A1:Z1, 0)))

To exclude the midterm:
=Sum(Sheet1!A5:Index(A5:Z5, Match("m", Sheet1!A1:Z1, 0)-1))

To sum all "assignments" prior to the first midterm, you could try:
=SUMIF(A1:INDEX(A1:Z1,MATCH("m",A1:Z1,0)),"a",A5:Z 5)

To sum all "quizzes" prior to the first midterm, you could try:
=SUMIF(A1:INDEX(A1:Z1,MATCH("m",A1:Z1,0)),"q",A5:Z 5)


Thank you both for your suggestions.

JMB, your last two suggestions work perfectly!!!
It was exactly what I was looking for!
I have been working on this for the better part of the day. I have
used the SUMIF, and in my search today, I found the MATCH, but I never
came across the INDEX. I could have spent weeks or months before I
came up with this, if at all.
It's magic!

Thank you for your wonderful assistance!