Help? SUM row to a cell with a specific value in a different row

[DIY Guy]

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!

 

[Tom Ogilvy]

=indirect("D5:" & address(5,match("m",A1:AA1,0),4,true))

To Include F

=indirect("D5:" & address(5,match("m",A1:AA1,0)-1,4,true))

not to include F

=sum(indirect("D5:" & address(5,match("m",A1:AA1,0),4,true)))

 

[Guest]

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:Z5)

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

 

[DIY Guy]

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:Z5)

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

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!

 

[Guest]

You're quite welcome.

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!