Yes it can be done.

Try this here:

```
=SUM(INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1)):INDIRECT(ADDRESS((CELL("row",B1))*7,1)))
```

**Edit:**

thanks to Mike Woodhouse (see his answer) I can simplify my solution a bit.

`Row()`

(in the cell "B1") will do the same than `CELL("row",B1)`

so

```
=SUM(INDIRECT(ADDRESS((ROW()-1)*7+1,1)):INDIRECT(ADDRESS(ROW()*7,1)))
```

will do the same a bit simpler.

Explanation:

You need to calculate the addresses then.

`(CELL("row",B1)-1)*7+1`

will give you the start cell. `CELL("row",B1)`

gives me the number of the current row. Subtract 1 multiply by 7 and add 1 will give for each row the starting daily row as a number.

`ADDRESS((CELL("row",B1)-1)*7+1,1)`

is making an address string from numbers, I give the calculated starting row number and the column (the 1 at the end)

`INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1))`

indirect is making a range from a string.

The same than for the last wanted cell. `INDIRECT(ADDRESS((CELL("row",B1))*7,1))`

the difference is only the calculation of the ending row.

Then I give the whole thing to `Sum`

and I am done. Just copy it down 52 times and you have the sum for every week.