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.