# Functions As SUM() Parameters/Arguments? [closed]

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:

If I put

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2017-06-08 21:56:51.268531

Sort by » oldest newest most voted

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.

more

Thanks, Lupp!

( 2017-04-16 23:56:43 +0100 )edit

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

more