# 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?

Yes, it's called an open-ended reference. For example `A:A`.

# Explanation

1. In "A1 notation", cell addresses include a letter to specify the column and a number to specify the row.
2. A range reference is made by combining two cell references separated by a colon `:`. The first is the range's starting cell, and the second is its ending cell.
3. `A:A` is an open reference (range) that specifies the whole of column A
• `A2:A` is a reference closed at one end, `A2`, but open at the other, `A`.

``````SUM(A10:A500)
``````

Use:

``````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. Commented Dec 7, 2016 at 17:03
• What versions of Excel and LibreOffice are you using? Commented Dec 7, 2016 at 17:58
• Good point. My software versions are LibreOffice (basically the latest stable) 5.2.3.3, and Excel 2007. Commented 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. Commented 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
Commented 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. Commented Dec 5, 2016 at 2:43