Ask Your Question
0

Sum of a row of a named range

asked 2018-06-06 21:33:49 +0200

hjek gravatar image

updated 2018-06-06 21:34:53 +0200

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2018-06-07 10:23:02 +0200

Lupp gravatar image

updated 2018-06-07 10:24:52 +0200

<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.)

edit flag offensive delete link more

Comments

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();))
hjek gravatar imagehjek ( 2018-06-07 12:35:41 +0200 )edit
1

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.)

Lupp gravatar imageLupp ( 2018-06-07 12:49:38 +0200 )edit

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.

hjek gravatar imagehjek ( 2018-06-07 15:43:29 +0200 )edit
1

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 gravatar imageLupp ( 2018-06-07 21:13:03 +0200 )edit

@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.)

hjek gravatar imagehjek ( 2018-06-08 13:29:07 +0200 )edit
1

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.

Lupp gravatar imageLupp ( 2018-06-08 14:47:06 +0200 )edit

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.

hjek gravatar imagehjek ( 2018-06-08 15:14:14 +0200 )edit
1

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.

Lupp gravatar imageLupp ( 2018-06-08 15:51:24 +0200 )edit
1

answered 2018-06-07 07:27:37 +0200

pierre-yves samyn gravatar image

updated 2018-06-07 07:30:03 +0200

Hi

For example:

=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))

Note:

  • Here 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)
  • So adapt COLUMN to the actual number of columns in your range.

SumRowRange.ods

HTH - Regards

edit flag offensive delete link more

Comments

We can again use ROW in the third parameter (instead of COLUMN) - =SUMPRODUCT(INDEX(foo;ROW(A1); ROW($A$1:$A$3))) :-)

JohnSUN gravatar imageJohnSUN ( 2018-06-07 07:37:31 +0200 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2018-06-07 08:57:20 +0200 )edit

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 gravatar imagehjek ( 2018-06-07 12:31:10 +0200 )edit
1

@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 :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2018-06-07 15:15:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-06 21:33:49 +0200

Seen: 88 times

Last updated: Jun 07 '18