4

I have a function like this in Google Spreadsheet:

SUM(A10:A500)

I'm using 500, but if it's possible I want to use a constant that represents end of the column A (i.e., the last row of a column that contains data/numbers).

Is there such a constant or formula?

migrated from superuser.com Jul 11 '16 at 22:15

This question came from our site for computer enthusiasts and power users.

3

Short answer

Use an open ended reference.

Explanation

A range reference has two parts, the address of the start cell and the address of the end cell. On the A1 notation usually the cell address includes a letter to specify the column and number to specify the row.

  • A:A specifies the whole column
  • A2:A specifies that the start cell is A2 and that the last cell is the last one of the A column.

For the specific case in the question, instead of

SUM(A10:A500)

try

SUM(A10:A)
  • This is the right answer for GoogleDocs. Unfortunately, the right answer is not portable. This formula gives a Name Error on MSOffice or LibreOffice. If you download the Google Spreadsheet as (e.g.) xlsx, you'll see the formula will be changed to something like sum(A10:A1000). Hopefully, this gets fixed in the MS and Libre office applications in the future. – jehad Dec 7 '16 at 17:03
  • What versions of Excel and LibreOffice are you using? – Rubén Dec 7 '16 at 17:58
  • Good point. My software versions are LibreOffice (basically the latest stable) 5.2.3.3, and Excel 2007. – jehad Dec 11 '16 at 23:06
  • @jehad: Regarding Excel 2007, I'm sorry, I don't have that version, but here is a related question on Stack Overflow about how to reference whole columns on Excel 2007: stackoverflow.com/q/9100769/1595451. Regarding portability, please post a new question including a demo spreadsheet and the steps to reproduce the problem. – Rubén Dec 12 '16 at 2:52
2

Here is one way I tend to use for this kind of problem:
Based on your example, the solution would be:

=SUM(A10:INDIRECT(CONCATENATE("A",MATCH(999999999,A:A,1))))

How this works:

The interesting part is: INDIRECT(CONCATENATE("A",MATCH(999999999,A:A,1)))

  1. INDIRECT is returning a reference based on the given string.
  2. Concatenate("A",...) is creating a cell reference string, with "A" as the column, and the row number given by the next part.
  3. MATCH(999999999,A:A,1) returns a position in the array A:A (i.e. the whole of column A), where the value is less than (the 1 at the end specifies the search must be "less than") the number 999999999. Because this number is SO big (and you can make it even bigger or the max possible, depending on your requirements and your data set), the MATCH will search to the end of the array (A:A in this case), and return the position of the last value smaller than the big number, which will always be the last row in the column (because the 999999999 is SO big).
  • I just posted an answer with a simpler way. – Rubén Dec 5 '16 at 2:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.