# Calc: semi-open cell range? [closed]

Here is a challenge: first line contains title of column, i.e. decoration text which is not processed; rows 2 to n contain numbers and row n+1 is the sum of the preceding numbers as e.g. in column b, row 51 `=SUM(B$2,B50)`

. Note the mixture of relative and absolute references which allows to insert new rows without the need to modify the formula.

I'd like to have the sum immediately below the title so that row 1 shows the title, row 2 the sum and rows 3+ are the data. In this variant, the first two rows can be frozen and the sums are always visible when you scroll across the data. For convenience, the sum formula should not need to be modified when new data rows are added. Two workarounds are available for that.

*Closed reference*: use an arbitrarily large limit like`=SUM(B$2,B99999)`

(other answers on this site give an even larger limit, probably equal to the internal bound, but the exact value is not an issue)*Open reference*: put the raw data in every other row without any "external" information, moving title and sum to the preceding column. Sum in A2 becomes`=SUM(B:B)`

which is more elegant, but layout turns queer

**Is there a way to specify a semi-open reference like (the erroneous) =SUM(B2:B)?**

This notation is not accepted by LO and ends up with `#NAME?`

because of `B`

The answer hinted by @Lupp is to create a flag column left void for "active" rows and containing some character for discarded rows. Formula

`=SUMPRODUCT(B:B;A:A="")`

where`B`

is the column to sum and`A`

the flag one.Sorry. I cannot recognise either my answer nor any essence from my comments in the comment above. Any visitor to this thread should feel encouraged to read my answr and everything themselves.