Duke Carey wrote...
Another way, entered in B5, for example, sums B1:B4:

=SUM(OFFSET(B5,-4,0,4,1))
....

For some reason the OP is trying to avoid using direct range
references. If the B5 in your offset call would be acceptable, why not
simplify to =SUM(B1:B4) ?