How to programmatically select a range

image_2020-12-03_22-35-20.png

I have an issue that I wasn't able to solve after a lot of googling:

I would like to get the SUM of cells C2:F17 in cell A1, and then I want the range to automatically increase every day. Meaning: tomorrow the range for A1 should be C2:F21, and in two days it should be C2:F25.

The closest I was able to get was to do

Please Login or Register  to view this content.

, but that just gives me a

Please Login or Register  to view this content.

.
Removing the "&" and the quotations around "C2:" gives me a syntax error…

I attached a minimal example file if you want to mess around with it.

Create your dynamic named range:
name: SumRange
refers to: =シート1!$C$2:INDEX(シート1!$F:$F,MATCH(9E+100,シート1!$F:$F))
a1=SUM(SUMRANGE)