Calculated range in Google Sheets. How to wrap a formula?

Question

excel

There are formulas that calculate the letter of the last column of an array based on the presence of headers in the first row:
=REGEXEXTRACT(ADDRESS(COUNTA(B2);COUNTA(A1:1);4); ".")
or
=LEFT(ADDRESS(COUNTA(B2); COUNTA(A1:1);4))
In both cases, we get the letter of the last column that has entries in 1 row, for example H.

But both of these formulas do not work if they are part of another formula, for example this one:
=SUM(G :LEFT(ADDRESS(COUNTA(B2);COUNTA(A1:1);4)))
Instead of the sum of columns G:H, an error appears: Unknown name of the range G.

Task worth: use in ARRAYFORMULA (or SUM or any other ) is the calculated end of the range, since there are an unknown number of columns in the table, and they will be added.

I found on the forums that in such cases the formula needs to be wrapped in curly brackets, like this:
=SUM(G:{LEFT(ADDRESS(COUNTA(B2);COUNTA(A1:1);4))})

But I have this option does not work.

Answer 1

You need an INDIRECT
function Put an address in it - get out of range values