How do I sum a column based on another column

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.