11

I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration:

        A                    B      C
1    =SUM(A2:Ainfinite)
2     1234
3     5678
...

I could write A2:A1048576 which would work to some degree, but it would not be elegant or foolproof. Is there any notation to express the range from A 2 to A infinite?

|improve this question
  • You should really check to see if the question has been asked before before posting a bounty. – Excellll Jun 23 '14 at 13:01
  • @Excellll You should really read. That is about Excel, this is about LibreOffice Calc. – Notinlist Jul 19 '15 at 19:21
9
+50

Ehm, I am risking to say something silly here, but why don't you just

Get total number of rows:

ROWS(A:A)

Convert to coordinate of the last cell using INDIRECT:

(INDIRECT("A"&(ROWS(A:A))))

And use it in your SUM formula

 SUM(A2:(INDIRECT("A"&(ROWS(A:A)))))

I cannot guarantee that this is going to work, as I am currently logged into my Windows machine. But it works on MS Excel.

UPDATE: as correctly noted by tohuwawohu you will need to set formula syntax to Excel A1

|improve this answer
  • 2
    This works with LibreOffice, too - provided that the formula syntax is set to Excel A1. In contrast to this answer, the solution calculates the last possible index without "hardcoding" it into the formula. Nice! – tohuwawohu Jun 24 '14 at 13:56
  • Is there a way to stop this breaking whenever I add a new row? – HorusKol May 21 '16 at 10:18
3

With a current version of Libreoffice Calc (tested with 4.2), you can address the complete Column A with A:A (if Formula syntaxin Tools -> Options -> Calc -> Formula is set to Excel A1).

But AFAIK there's no way to reference a difference (complement), something like "A:A without A1". It would be great if =SUM(OFFSET(A:A;1;0)) would work, but it doesn't.

|improve this answer
  • I wasn't aware of that formula setting in Calc. This also works with version 3.5.4. Thanks. – fixer1234 Nov 12 '15 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.