[SOLVED] Google sheets summation formula, iterating until a new header on column A

Google sheets summation formula, iterating until a new header on column A

I am creating a table in google sheets to calculate my weighted grades, and overall grades for each module. I have attempted to create a formula to sum-up the weighted grades column (E), for each row until the next module is detected (Column A non blank), and place the sum in column F.

Desired Table Output:

	A	B	C	D	E	F
1	Module	Weight	Type	Grade	Weighted Grade	Total
2	Algorithms	10%	CW1	92%	9.2%	77.825%
3		10%	CW2	86.25%	8.625%
4		80%	Exam	75%	60%
5	Software Engineering	20%	CW1	64%	12.8%	64.8%
6		80%	Exam	65%	52%

As you can see the total should be the sum of the weighted grades until the next module title.

The formula (placed in F2):

=ARRAYFORMULA(
  IF(
    A2:A <> "",
    ARRAYFORMULA(
      SUMIF(
        ROW(A:A),
        ">="&ROW(A2:A),
        OFFSET(
          E:E,
          ROW(A2:A) - ROW(A2),
          0,
          MATCH(
            TRUE,
            ISBLANK(A3:A),
            0
          ),
          1
        )
      )
    ),
    ""
  )
)

The issue is currently the sums are calculated to the bottom of the table, and don't stop when a new module starts. i.e.

	A	B	C	D	E	F
1	Module	Weight	Type	Grade	Weighted Grade	Total
2	Algorithms	10%	CW1	92%	9.2%	142.625%
3		10%	CW2	86.25%	8.625%
4		80%	Exam	75%	60%
5	Software Engineering	20%	CW1	64%	12.8%	64.8%
6		80%	Exam	65%	52%

As you can see, F2 is the sum of all weighted grades, not just algorithms ones.

I have tried several different formulas, but I am unexperienced in the language.

Note:

I have attached images of the tables, as it appears they are not rendering correctly when I submit the question.

Solution

You may try:

=map(A2:A,lambda(Σ,if(Σ="",,sumif(scan(,A:A,lambda(a,c,if(c="",a,c))),Σ,E:E))))