If you want an expression that evaluates to SUM($D$55:$E$67)
when it’s evaluated on rows 55-67, and evaluates to SUM($D$68:$E$80)
when it’s evaluated on rows 68-80, etc., use
SUM(OFFSET($D$3, 13*INT((ROW()-3)/13), 0, 13, 2))
(Insert this into your full formula.) Row numbers 55 through 67 get converted to 52 through 64 (by subtracting 3), which yield 4 through 4.923 (by dividing by 13), all of which get converted to 4 by INT()
. Then multiply by 13, to get back up to 52, and use that as an offset to D3
to get up to D55
.
Or, to add the values in Columns D and E for rows having the same value in Column A as the current row, use
(SUMIF($A$1:$A$999, $A1, $D$1)+SUMIF($A$1:$A$999, $A1, $E$1))
which does pretty much exactly what I just described. Replace 1 and 999 with the range of rows in which you have class data.
It seems like it should be possible to combine the above into a single SUMIF
(or maybe SUMIFS
) that sums Columns D and E, but I couldn’t figure it out.