I am creating a grade sheet where I would like to SUM D5:Z5 on Sheet
1, up to the point where a "m" appears in row 1 on the same sheet.
This SUM will be used in a function in Sheet 2.

For example:
The letters in row 1 (table below) can vary. They represent:
a = assignment
q = quiz
m = midterm

Sheet 1 (currently labeled "Input Student Grades") looks like this:


A B C D E F G H .....
1 a q a a q m a q
2
3
4
5 10 20 10 10 20 10 35 45


Since the "m" appears in Column "F" (cell F1), I would like the SUM to
stop at, and possibly include F5 (depending on which value I am
adding).

The values in row 1 are in a data validation drop-down, so the "m" can
essentially appear anywhere from A1 to Z1.

I am trying to calculate a midterm grade in a sheet where there can be
any number of assignments or quizzes before the midterm.

This SUM will be used in a calculation on Sheet 2 (now called
"Summary").

What I am ultimately trying to accomplish is:
SUM up all the values from A5:Z5 where "a" appears in row 1 (ie,
A5+C5+D5=30),
SUM up all the values from A5:Z5 where "q" appears in row 1 (ie,
B5+E5=40)
....and so on.


I am looking for a purely "excel" solution. I am sure there is a VB
solution, but VB is a little beyond my expertise right now.

Any help you could provide would be appreciated.

Thanks!