12

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?

Notinlist
  • 780

2 Answers2

9

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

harrymc
  • 480,290
Art Gertner
  • 7,279
  • 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
4

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.

tohuwawohu
  • 10,678