Google-sheets – SUM weekly values according to date in one column A and values in column B

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datestracking

As shown in the screenshot, I have values I am looking to add for weekly totals to track, however, this is very tedious having to create a new code for every week, especially as the weeks change per month and so on. What I am looking to do is use the date feature as I have lined up in column A to match with column b and so on to make weekly values so I only have to type in the sum of a week number rather than a sum of ex b2:b8

I was thinking because if I were to type =ISOWEEKNUM("July 1") it gives me 27, so I am looking to use some code to say =SUM(week27in column a, B:B) type of thing but I don't know Google Sheets code well enough to think of it on my own.

If anyone has ideas please let me know.

Best Answer

Delete range A2:A and paste this into A2 cell and for next month just type August in A1 cell:

=ARRAYFORMULA(TEXT({ROW(INDIRECT("A"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 1))&":"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 7))));   "Week 1"; ROW(INDIRECT("A"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 8))&":"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 14))));  "Week 2"; ROW(INDIRECT("A"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 15))&":"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 21))));  "Week 3"; ROW(INDIRECT("A"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 22))&":"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 28))));  "Week 4"; ROW(INDIRECT("A"&
 DATEVALUE(DATE(2019, MONTH(A1&1), 29))&":"&
 DATEVALUE(DATE(2019, MONTH(A1&1), DAY(
 EOMONTH(DATE(2019, MONTH(A1&1),1),0)))))); "Week 5"}, "mmmm d"))

Related Solutions

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

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.

Google Sheets – How to Group Column Data and Display Values in a Row

You can accomplish this in two steps:

Create a new sheet Sheet2 and use the following formula for A1 (or wherever you want the column to begin): =unique(Sheet1!A:A). This will give you a vertical column of simply A, B, C, and D as the unique values in Sheet1's A column.
Combine transpose and filter in the cell adjacent to each cell in Sheet2 as follows: =transpose(filter(Sheet1!B:B,Sheet1!A:A=A1)). Here, you are filtering the second column of your Sheet1 according to whether or not the letter next to in Sheet1 matches the latter next to it in Sheet2. This normally prints vertically, so you use transpose to make it print horizontally, i.e. in the same row as you show above. Then, drag the formula down to apply it to each successive row.

The formulas in Sheet2 end up looking like this:

Best Answer

Related Solutions

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

Google Sheets – How to Group Column Data and Display Values in a Row

Related Topic