How can I use ARRAYFORMULA to sum every 7 rows?

Question

I'm trying to use an arrayformula to sum weekly values. I have a table like:

     A         B        C
   date    | value | weekly total
---------------------------------
2016/08/28 |   5   |
2016/08/29 |       |
2016/08/30 |   5   |
2016/08/31 |   5   |
2016/09/01 |   5   |
2016/09/02 |       |
2016/09/03 |       | <should be 20>

I want the C column to calculate the sum of the B column for the past week each Saturday.

I have =ArrayFormula(IF( EQ(weekday(A2:A),7), SUM(B2:B), ""))
I would think there should be a simple way to tell SUM to take from 6 rows back to the current row, but I haven't found anything.

Another option is:
=ArrayFormula(IF( EQ(weekday(A2:A),7), SUMIF(WEEKNUM(A2:A),WEEKNUM(A2:A),B2:B), ""))
but that breaks when there's more than one year on the sheet.

Any ideas?

Answer 1

Phew that actually took me a while to work out. Your formula in cell C9 is:

=if(weekday(A9)=7,SUM(INDIRECT("R[-6]C[-1]",FALSE):INDIRECT("RC[-1]",FALSE)),"")

You can copy that all the way up/down column C.

You end up with this:

A          |   B   |    C
Date       | value | weekly total
---------------------------------
2016/08/28 |   5   |
2016/08/29 |       |
2016/08/30 |   5   |
2016/08/31 |   5   |
2016/09/01 |   5   |
2016/09/02 |       |
2016/09/03 |       |    20
2016/09/04 |   4   |
2016/09/05 |   3   |
2016/09/06 |   6   |
2016/09/07 |       |
2016/09/08 |       |
2016/09/09 |       |
2016/09/10 |       |    13
2016/09/11 |       |
2016/09/12 |       |
2016/09/13 |       |
2016/09/14 |   4   |
2016/09/15 |   3   |
2016/09/16 |   1   |
2016/09/17 |       |    8
...etc.

Answer 2

Short answer

Alternative 1

• Use the system 2 for week numbering.

Alternative 2

• 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.

Explanation

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 0; also "" 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),
B2:B), ),LEN(A2:A)))

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.

Assuming that

1. the Table is a continuous range
2. there is no other data below the Table
3. the Table headers are on Row 1
4. the Table values start on Row 2
5. the dates are continuous, start on Sunday and end on Saturday
6. 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.

=ARRAYFORMULA(IF(FILTER(MOD(ROW(A2:A)-1,7),LEN(A2:A))<>0,,
MMULT(ARRAY_CONSTRAIN(IF(N(MOD(ROW(A2:A)-1,7)=0)*TRANSPOSE(N(MOD(ROW(A2:A)-1,7)>=0))*
N(ROW(A2:A)-1>=TRANSPOSE(ROW(A2:A)-1))-(N(ROW(A2:A)-1>=TRANSPOSE(ROW(A2:A)-1+7)))>0,1,0),
COUNT(A2:A),COUNT(A2:A)),FILTER(IF(B2:B="",0,B2:B),LEN(A2:A)))))

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.