# Is there a constant to represent "last row of a column" 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?

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. Dec 7, 2016 at 17:03
• What versions of Excel and LibreOffice are you using?
– Rubén
Dec 7, 2016 at 17:58
• Good point. My software versions are LibreOffice (basically the latest stable) 5.2.3.3, and Excel 2007. Dec 11, 2016 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, 2016 at 2:52
• This type of range does not appear to work for the protected sheets/ranges. However one can work around it by protecting a sheet, adding exceptions for entire columns like `B:B`, then adding another rule to protect parts of that column like `B1:B2`.
– Walf
Feb 4, 2020 at 5:45

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, 2016 at 2:43