Finding a sum of cells given address range in google sheets

Question

I need a google sheet formula

Click here to make a copy of sheet

I have a data in table from B column to H column Data runs from left to right only in B through H Given a starting address of the cell and given a ending address of the cell (in the image you see H1 and H2 columns)

I want to find the sum of all cells from the start to end address with respect from B through H

When yellow value dates are changed the sum should be reflected accordingly.

My results are incorrect since it needs to find the sum spanning the cells from B to H column

I have tried with

=SUM(INDIRECT(H1&":"&H2))

Answer 1

Updated answer:

=sum(filter(tocol(B5:index(H5:H,D2)),isbetween(--tocol(sequence(D2)&sequence(1,7)),--(D1&F1),--(D2&F2))))

Answer 2

Try

=let(cols,tocol(B5:H,1),start,(D1-1)*7+F1,end,(D2-1)*7+F2,sum(chooserows(cols,sequence(end-start+1,1,start))))

Answer 3

OFFSET() may work with few other functions. Try-

=LET(
UB,REGEXEXTRACT(H2,"\d+"),
LB,REGEXEXTRACT(H1,"\d+"),
x,SUM(OFFSET(INDIRECT(H1),,,UB-LB,7)),
y,SUM(OFFSET(INDIRECT(H1),UB-LB,,1,COLUMN(INDIRECT(H2))-COLUMN(INDIRECT(H1))+1)),
x+y)