Google-sheets – How to use ARRAYFORMULA to sum every 7 rows

google sheets

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?

Best Answer

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.

Related Solutions

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+

Google-sheets – How to find the sum of specific rows in Column A if rows in Column B meets a given requirement in Google Sheets

Create a Pivot table (under Data) with C for Rows and A and B (Summarise by SUM) for Values.

Best Answer

Related Solutions

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

Google-sheets – How to find the sum of specific rows in Column A if rows in Column B meets a given requirement in Google Sheets

Related Topic