Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I sum a column based on another column
I am trying to sum grades for a class. When recording the grades I have a
grade for in class and one for homework so the column headings alternate between the date, HW(for homework) and a possible column for projects. I want to total each of the three types of grades seperately so I need to use the column heading to determine if the column is added and it is fine for the HW and project columns but I want to write the equation so that the in class grade is added if the heading is a date. I hope this makes sense. |
#2
|
|||
|
|||
How do I sum a column based on another column
Hi,
You could use the fact that dates (column headings for InClass grades) are numbers while the other two column headings (HW and Project) are text strings. So the summing criterion could be, "ISNUMBER(headings-range)". This would return TRUE for InClass and FALSE for HW and Project. So the formula could be something like, =SUMPRODUCT(ISNUMBER($B$2:$M$2)*(B3:M3)) The formula would add grades in B3:M3 from only those columns for which the column headings (B2:M2) happen to be dates (and exclude those for which column headings are not dates). I don't know what kind of formulas you are using for summing up HW and P grades. The following formulas would also work. =SUMPRODUCT(($B$2:$M$2="HW")*(B3:M3)) =SUMPRODUCT(($B$2:$M$2="P")*(B3:M3)) Regards, B. R. Ramachandran "spepperchin" wrote: I am trying to sum grades for a class. When recording the grades I have a grade for in class and one for homework so the column headings alternate between the date, HW(for homework) and a possible column for projects. I want to total each of the three types of grades seperately so I need to use the column heading to determine if the column is added and it is fine for the HW and project columns but I want to write the equation so that the in class grade is added if the heading is a date. I hope this makes sense. |
#3
|
|||
|
|||
How do I sum a column based on another column
I tried that but it didn't work. When I tried it I was given an error message. I've made it work but I wan't to make it easier to add more columns so what I've done gives the answer but will be very tedious as the year progresses. Thanks for the post though. "B. R.Ramachandran" wrote: Hi, You could use the fact that dates (column headings for InClass grades) are numbers while the other two column headings (HW and Project) are text strings. So the summing criterion could be, "ISNUMBER(headings-range)". This would return TRUE for InClass and FALSE for HW and Project. So the formula could be something like, =SUMPRODUCT(ISNUMBER($B$2:$M$2)*(B3:M3)) The formula would add grades in B3:M3 from only those columns for which the column headings (B2:M2) happen to be dates (and exclude those for which column headings are not dates). I don't know what kind of formulas you are using for summing up HW and P grades. The following formulas would also work. =SUMPRODUCT(($B$2:$M$2="HW")*(B3:M3)) =SUMPRODUCT(($B$2:$M$2="P")*(B3:M3)) Regards, B. R. Ramachandran "spepperchin" wrote: I am trying to sum grades for a class. When recording the grades I have a grade for in class and one for homework so the column headings alternate between the date, HW(for homework) and a possible column for projects. I want to total each of the three types of grades seperately so I need to use the column heading to determine if the column is added and it is fine for the HW and project columns but I want to write the equation so that the in class grade is added if the heading is a date. I hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format based on data from another column | Excel Discussion (Misc queries) | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) | |||
increment a column based on information in another column | New Users to Excel | |||
Count based on another column | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |