Functions As SUM() Parameters/Arguments?

[jseymour]

I can’t count the number of times I’ve tried to figure this out. Search? I’ve searched and searched and searched. Finally I must ask.

LibréOffice version is inconsequential. I’ve had this problem with every version I’ve ever used.

Given a column you wish to sum(), where rows may be added, why does this not work:

=SUM(D3:ADDRESS(ROW()-1,4,3,1))

If I put

=ADDRESS(ROW()-1,4,3,1)

into, say, cell D35, I’ll get “D34”. So why does my SUM(), above, not give me the result of =SUM(D3:D34)? Instead I get “Err:502,” “Invalid Argument.” What am I, or LibréOffice Calc, doing wrong?

Thanks,
Jim

[Lupp]

(In addition)
SUM() accepts constants or References on its parameter positions. Texts given as constants raise an error, texts introduced via references are ignored. Numbers (whether scalar constants, results of scalar expressions, elements of arrays calculated by array expressions, or introduced via references) are added.
The result of an ADDRESS expression is text, not a reference. Since D3 is a reference and there is no operator to combine a reference with text, D3:ADDRESS(Something) cannot be anything meaningful.
In a strange case where you actually need to calculate a reference there are two ways:

1. OFFSET(GivenReference;RowsOffset;ColumnsOffset;NewHeight;NewWidth)
2. INDIRECT(CellAddressOrRangeAddressFormedAsAtext)

Also:
Using =ADDRESS(ROW()-1,4,3,1) you would never get D34, but probably $D34. To get the address relative concerning the column as well you would have to use =ADDRESS(ROW()-1,4,4,1).
To obtain the reference originally pursued INDIRECT("D3:"&ADDRESS(ROW()-1,4,4,1)) was the approppriate expression.

[jseymour]

Thanks, Lupp!

[karolus]

actually nobody knows why the hell you don’t use simply =SUM(D3:D34) ???
This Formula expands “automagically” aslong you’re insert additional Rows between Rows 3 and 34

[jseymour]

Because new rows were appended to the end of the existing rows. How, odd, no?