Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help? SUM row to a cell with a specific value in a different row
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help? SUM row to a cell with a specific value in a different row
=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))) -- Regards, Tom Ogilvy "DIY Guy" wrote in message oups.com... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help? SUM row to a cell with a specific value in a different row
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) "DIY Guy" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help? SUM row to a cell with a specific value in a different row
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help? SUM row to a cell with a specific value in a different r
You're quite welcome.
"DIY Guy" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not allowing to continu unless a specific cell has specific answer | Excel Discussion (Misc queries) | |||
Link to specific cell in specific Excel file | Excel Discussion (Misc queries) | |||
Link from a specific Cell in Excel to a specific para. in Word | Excel Worksheet Functions | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) |