Home>

### i want to aggregate the daily numbers in excel on a separate sheet by week

I want to aggregate the daily numbers in Excel on a separate sheet by week.

---- A B C D E F G H I J
--- 4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 4/9 4/10 ・ ・ ・ ・
1 | 1 0 2 2 3 3 1 1 1 2
2 | 1 0 2 2 3 3 1 1 1 0

The numbers are summarized by day as above.
I'm trying to put together a week on a separate sheet.
The weekly table is complete, just add a function
You can never shift the total range by 7

For example, if I want to put together a week from 4/1 to 4/7,
If it is sum, the first week can be summed with sum (A1: G1).
Even if you use autofill, the second week will only deviate from sum (B1: H1).
Is there any good way?

sheet1

sheet2

``= SUM (OFFSET (Sheet1! \$B \$3, ROW ()-2, (COLUMN ()-2) * 7,1,7))``

Copy this to all the data parts of the weekly data. Perhaps that is the solution.
As I wrote before,

>>The weekly table is complete,
If the table is out of alignment, adjust the ROW ()-2 and COLUMN ()-1 parts. If you add it to the value of ROW/COLUMN at the upper left corner of the table and correct it to 0, it should pass

Check OFFSET yourself

old content

What about A2 on the second sheet?

``= SUM (OFFSET (Sheet1! \$A \$2, ROW ()-2, (COLUMN ()-1) * 7,1,7))``

>>The weekly table is complete,
If the table is out of alignment, adjust the ROW ()-2, COLUMN ()-1part. If you add it to the value of ROW/COKUMN at the upper left corner of the table and correct it to 0, it should pass

By the way, if you want the date, set it to A1

``= CONCATENATE (TEXT (OFFSET (Sheet1! \$E \$5,0, (COLUMN ()-1) * 7), "m/d"), "to", TEXT (OFFSET (Sheet1! \$E \$5,0, (COLUMN ()) * 7-1), "m/d"))``

(However, if the date in the daily data sheet continues from E5 to F5 as 6/1, 6/2, 6/3 ...)