Formula to SUM cells to the left?

Question

I need a Sheets formula for a Year to Date field. Essentially, I need to sum up the cells to the left of a given cell, up and to the 11 previous cells (there are multiple years in a single row). In Excel, this is accomplished via OFFSET and a -Width value. Sheets won't play nice with -Width.

Does anyone have any suggestions?

For what it's worth, the formula that doesn't work in Sheets (but does in Excel) is as follows:

=SUM(OFFSET(Forecast!$B$12,ROW($A13)-ROW($A$12),MATCH(Report!$I$1,Months,0)+2,1,-$I$2))

Where -$I$2 is a variable number of months field.

Answer 1

I used indirect and the RC format. This secret sauce is the max(COLUMN()-11,1).

=sum(INDIRECT("R"&row()&"C"&max(COLUMN()-11,1)&":R"&row()&"C"&(COLUMN()-1), FALSE))

or with your $I$2 cell

=sum(INDIRECT("R"&row()&"C"&max($I$2-11,1)&":R"&row()&"C"&($I$2-1), FALSE))

Edit:

With the additional info, assuming your entry is in br, your first month is in bc, the sheet your interested in is sh and you want to go back $I$2-1 columns (because bc counts as one)

=SUM(INDIRECT("sh!"&"R"&br&"C"&(bc+1-$I$2)&":R"&br&"C"&bc,false))