Ask Your Question

Calc: semi-open cell range? [closed]

asked 2017-05-07 09:12:20 +0200

ajlittoz gravatar image

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.

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by ajlittoz
close date 2017-05-11 08:13:20.316500


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.

ajlittoz gravatar imageajlittoz ( 2017-05-11 08:10:49 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-05-11 10:04:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-07 11:26:57 +0200

Lupp gravatar image

updated 2017-05-07 11:51:45 +0200

To compute SUM above the numeric data is something I would always prefer (except, probably, when working with a pencil on a sheet of paper). To disambiguate things I would, however, use one header row more: A label like "Total", then SumResult (fomula), then a label like "Amounts".

To get automatic adaption of the range for summing there are (at least) 3 ways.
1) Dedicate one empty row below the data to remain empty but included in the RangeToSum. You then can always insert rows above this "dummy" which will automatically be included. Error-prone.

2) Enable the option 'Tools'>'Options'>'LibreOffice Calc'>'General'>'Expand references when new columns/rows are inserted' and insert additional rows immediately below the current data. Errors in different contexts expected.

3) Maintain a few META data about your table of data, resulting at least in a counted ActualNumberOfUsedContiguousRows. Base your ranges on this result then using OFFSET (or in specific cases probably ADDRESS and INDIRECT).
See this demo concerning 3).

"Semiopen" (doubtable term) or "partly absolute" is not actually relevant in the context. Also absolute range references are expanded automatically if one of the ways 1), 2) is gone.

edit flag offensive delete link more


"Semiopen" is a clumsy word referring to maths, hinting that the bound (here "infinite" vs. finite) is included or not in the range.

There may be a simpler solution with sheet tabs: one of them contains the "amounts" and another one is the summary one where I can use =SUM(b:b). However, I'm in trouble when I try to add a heading row. I thought about =SUMIF(a:a;"^$";b:b) flagging the "decoration" rows with anything, e.g. * or 1, in column A, but the regexp does not match on empty cells.

ajlittoz gravatar imageajlittoz ( 2017-05-08 08:55:32 +0200 )edit

Other comparison criteria like "<>""""" do not work as well. "ISBLANK()" is also incorrect because there is no argument to point to the current cell (and it is no comparison either). How can I match on empty cells so that I only flag the few comment rows?

Note: this is academic because I could =SUM(b2:b104...).

ajlittoz gravatar imageajlittoz ( 2017-05-08 09:01:15 +0200 )edit

I must have missed something. As I read it, the OriginalQuestion did not contain anything about conditions or 'Criteria'. Except for the "semiopen" (which I knew from math) I also do not understand clearly your first comment here. Would you mind to explain the issue, whether academic or pragmatic?
Is it about the difference between having empty text returned by a formula and being blank?
Is it about how to get a criterion accepting exactly 'EmptyOrBlank'? (Did you study the attached demo?)

Lupp gravatar imageLupp ( 2017-05-08 11:53:36 +0200 )edit

Apologies, I was a bit fast: I explored another venue to see how I could avoid the issue of "demi-open" range and fall back to "full-open" range.

I haven't studied your demo because it displays errors when opened (#VALUE!) but it may be caused by the outdated LO version on the computer I use today.

My new path is to separate the "Total" summary from bulk data "Amounts" into different sheet tabs of the same document. Maybe it is a bad idea ergonomy-wise, I'll see that later.

ajlittoz gravatar imageajlittoz ( 2017-05-08 16:35:36 +0200 )edit

If the bulk data sheet contains only numbers, it works fine. But it is usually better to describe what the columns are for. Unfortunately, titles are not numbers and SUM(b:b) will display error #NAME?. I thought then to add a "disabling" column A where I'd put a flag like '*' or 'Z'' to tell the row does not contain numeric data and use SUMIF() to keep only numeric rows. I don''t intend to flag "normal" rows, which leaves the A-cell empty.

ajlittoz gravatar imageajlittoz ( 2017-05-08 16:42:00 +0200 )edit

Apparently, criterion "<>""Z""" in SUMIF() does not work on empty cell while standard formula =X9<>"Z" returns TRUE on an empty cell. Have also used regular expression "^$" but probably applies to Writer and not to SUMIF in Calc.

ajlittoz gravatar imageajlittoz ( 2017-05-08 16:45:13 +0200 )edit

Sorry. I don't unsertand that any more. What do you think to need the additional quotation marks for?
If this is meant to mean "Sum the range B:B for me only taking in account the rows where the cells in A:A are neither blank nor containing the empty text." you should use =SUMPRODUCT(B:B;A:A<>"") for clarity.
Ranges containing entire columns/rows in A:A/1:1 notation are accepted only by V5.0 or higher. Efficieny will suffer from huge ranges.

Lupp gravatar imageLupp ( 2017-05-08 17:33:59 +0200 )edit

Testing cells for not containing the one-letter-text Z using the 'Criterion' concept is done by "<>Z" in the respective parameter place. (Ctriteria are always evaluated into one string per cell to test.)

Lupp gravatar imageLupp ( 2017-05-08 17:38:34 +0200 )edit

Vielen Dank für Ihre Geduld!

The SUMPRODUCT is the pragmatic solution and it works for the initial question. (I feel rather comfortable with Writer, but I don't practice Calc enough and my question and comments show my lack of knowledge of built-in functions).

Please make your comment an answer so that I can and upvote it; it will be better than upvote the comment.

ajlittoz gravatar imageajlittoz ( 2017-05-09 08:13:52 +0200 )edit

Remark about the criterion in SUMIF and others: writing "<>Z" looks ambiguous to me unless testing for value of name Z is forbidden. This is why I wrote "<>""Z""" to get the 'formula' <>"Z" after interpretation to emphasize I want to test string Z and not the value of name Z.

From trials and errors, I see my intuition is wrong, but it gives a counter-intuitive criterion for empty cell as <> or =, reduced to the comparison operator.

ajlittoz gravatar imageajlittoz ( 2017-05-09 08:27:12 +0200 )edit

answered 2017-05-07 10:09:01 +0200

updated 2017-05-07 10:17:44 +0200

You may use AGGREGATE function that ignores AGGREGATEs including itself:


Yes, I understand that this isn't answering your direct question, just tries to solve your stated challenge.

There's no way to use "semi-open ranges" directly; one way would be to use stated Calc limits, like in SUM(B3:B1048576), where 1048576 is the largest row possible ATM; this works correctly with regards to row insertions and deletions, but that would possibly need to be corrected in the case of (possible) change of those stated limits.

edit flag offensive delete link more


Thanks for pointing me to AGGREGATE, but it effectively does not answer directly. Nevertheless, the function I did not know is interesting.

ajlittoz gravatar imageajlittoz ( 2017-05-08 08:15:09 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-07 09:12:20 +0200

Seen: 3,476 times

Last updated: May 07 '17