Home>
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

  • Answer # 1

    Answer when excel image

    = 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 ...)