Hi all,

I'm not very familiar with Google Sheets, and so hope that someone can point me in the right direction here.

I am quite surprised that some of the standard constructions which use OFFSET with one or more parameters comprising an array of values do not seem to work in Sheets.

For example, with A1, A2 and A3 containing 1, 2 and 3 respectively, both:

=SUM(N(OFFSET(A1,{0,1,2},0)))

and

=SUM(SUBTOTAL(9,OFFSET(A1,{0,1,2},0)))

return 6 in Excel, since they both resolve to:

=SUM({1,2,3})

In Google Sheets, however, both return 1, evidently since they are only processing the first cell in the range, i.e. A1.

I have tried committing the above as array formulas in Sheets, but this does not change anything.

Is it not possible to pass an array of values to OFFSET in Google Sheets? If so, this would seem to be a serious failing: part of OFFSET's versatility is its ability to accept arrays for one or two parameters, allowing us to construct multi-dimensional arrays of range offsets.

Hoping somebody more familiar with Sheets can clarify.

Regards