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?

2

2 Answers

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

2

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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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