# Libreoffice: sum of column except one cell

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?

• 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

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`

• 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

With a current version of Libreoffice Calc (tested with 4.2), you can address the complete Column A with `A:A` (if `Formula syntax`in 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.

• 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