---- 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?
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 yourselfold 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 ...)
- excel vlookup picks up different numbers
- [excel] numbers do not change even if pull-down list is selected
- i want to aggregate random numbers in java and display that number
- excel - [google spreadsheet] i want to aggregate the number of tweets for 6 months by vlook or sumif according to each value
- excel - specify the date and add up the numbers in the range
- excel - separate cells by
- Converting Excel column numbers to numbers and letters in Java
- Read and generate random numbers in excel and write to excel based on python
- excel python extract and aggregate specific regular expressions
- how to get numbers from top to bottom in excel vba
- excel - i want to separate date data extracted from html data by date and time using python
- specify excel pull-down list in a separate file without using indirect
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved