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 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
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.
