- Use the system 2 for week numbering.
- Use MMULT instead of SUM to do running total operations, but be careful on using it with open-ended references as they could make that spreadsheet recalculation takes a lot of time or even to break the spreadsheet.
ARRAYFORMULA usually is used to get multiple values (an array), but not all the functions works with ARRAYFORMULA. This is the case of SUM as it returns the same value for all the rows of the resulting array.
Tweek of the second OP's formula
The following formula returns 0 for rows below the Table
=ArrayFormula(IF( EQ(weekday(A2:A),7), SUMIF(WEEKNUM(A2:A),WEEKNUM(A2:A),B2:B), ""))
As the OP mentioned, the above formula doesn't work when the table has data along more than one year. This occurs because the week numbers repeats each year and the last days of the year and the first year of the next year could belong to the same week. Fortunately WEEKNUM has two systems for week numbering. The System 2:
The first week of the year is considered to be the week containing the first Thursday of the year, which is numbered as week 1. System 2 is the approach specified in ISO 8601, also known as the European system for numbering weeks.
By the other hand, by adding FILTER we could avoid the
"" could be removed to get a blank cell instead of a cell with 0 length string.
The tweaked formula is the following:
=ArrayFormula(FILTER(IF( EQ(weekday(A2:A),7), SUMIF(WEEKNUM(A2:A,21),WEEKNUM(A2:A,21),
Use of MMULT
MMULT could be used to do running total operations. For details on this, please checkout MMULT Usage, a spreadsheet by Adam Lusk.
- the Table is a continuous range
- there is no other data below the Table
- the Table headers are on Row 1
- the Table values start on Row 2
- the dates are continuous, start on Sunday and end on Saturday
- the data on Column A are values of type date, not strings
Adding following formula to C2, it will calculate automatically the weekly total the row corresponding to each Saturday.
To avoid that the use of open-ended references make troubles, FILTER and ARRAY_CONSTRAIN were included in the formula.
MOD, ROW, TRANSPOSE, compare operators (
>), multiply (
*) and subtraction (
-) operators are used make several matrixes which are used to identify which values will be include in the weekly total calculations and the row to put each one.
Multiplication is used instead of AND because it doesn't work on array formulas.