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?
Answer
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
No comments:
Post a Comment