7

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?

Improve this question

2 Answers 2

Reset to default
11

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)
Improve this answer
5
  • 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, 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.
    – jehad
    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
3

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).
Improve this answer
1
  • I just posted an answer with a simpler way.
    – Rubén ♦
    Dec 5, 2016 at 2:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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