In LibreOffice Calc, how can I sum a row of a named range?
Assume I have a named range, foo, that looks like this:
1 2 3
4 5 6
7 8 9
I’d like to add another column with a sum each row in foo, like this:
1 2 3 | 6
4 5 6 | 15
7 8 9 | 24
I am looking for a way to do this only by referring to foo, and not directly to the cell locations.
<kidding>As a retired teacher I may know too little about educational choices. But…</kidding>
I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by incomplete indexing. See this ornate demo.
(See also 6.14.6 of http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt.)
Nice. This is what I’m looking for. In particular the Only row index formula, but just changing the hardcoded row number to ROW(). Se the answer is:
=SUM(INDEX(foo;ROW();))What answer?
Anyway: Using the ROW() function without an argument returns the absolute row-number of the cell the formula is contained in. This may not be the relative number of the rows occurring in your range you want to address. INDEX() starts with row-number 1 for the first row of your range wherever that is in the sheet.
(I probably misunderstood you basically.)
Well, the absolute row-number of the cell formula is OK, because in the question I asked the cell formula is right next to the row it is summing.
Your answer was good enough for me to get it, even if you happened to misunderstand me a little bit.
Quoting @hjek: “…because in the question I asked the cell formula is right next to the row it is summing.”
This was exacly the reason for which I doubted if the logical difference between the ROW() of the sheet and the row of the range was regarded. Though it is not relevant as long as the named range starts in the first row of its sheet, it may be relevant tomorrow when, e.g, a row was inserted above the range to place headings there.
@Lupp: Ok, I see the problem now when inserting rows above the named range (but I didn’t run into that problem because my named range included the entire columns, which I should probably have been explicit about in the question.)
Assuming everything was clear and explicit:
In such a case I would surely prefer to use =SUM() with direct addressing instead of using a named range. This would read =SUM($A1:$C1) in the first row, and filling the formula down it would adapt automatically its (relative) row part of the range address.
True.
Yet the spreadsheet I’m currently working has more complicated formulae, so named ranges are useful for readability (or at least Joel Spolsky has convinced me of that). I was just trying to ask a more clear and general question, rather than one peppered with unnecessary complecting specificity.
Well, I never was patient enough to watch youtube for that kind of learning. From my own experience I would judge that named ranges can be useful, but actually are in rather rare cases. You will surely collect your own experiences…
However, where I have the actual data and the formulae in such a close relation (concerning the position) I would not expect much explanatory power in using a name. To define and use the name in different formulae elsewhere is possible anyway.
Hi
For example:
=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
Note:
A1 and $A$1:$C$1 are not the cell addresses. ROW and COLUMN functions generate numbers (e.g. 1 to 3 for COLUMN($A$1:$C$1)
HTH - Regards
We can again use ROW in the third parameter (instead of COLUMN) - =SUMPRODUCT(INDEX(foo;ROW(A1); ROW($A$1:$A$3))) 
Hi @JohnSUN
Yes, of course, since it is only a matter of generating numbers. It’s just an “educational” choice to not add difficulties of understanding. That said … it may also cause confusion, who knows
Regards
Thanks for answering so quickly.
I’m not sure I understand the idea in this answer as COLUMN($A$1:$C$1) still clearly refers directly to an unnamed cell range, which exactly what I’m trying not to do. If I wanted to refer directly to an unnamed cell range, the whole formula would just be =SUM(A1:C1), right?
@hjek - I had tried to anticipate this question by writing my first note. The ROW and COLUMN functions are only used here to generate numbers.
But no matter because @Lupp’s answer is better