Search code examples
google-sheetsindexingsyntaxcountgoogle-sheets-formula

A range joined with a formula with colons

I have this Google Sheets formula that I encountered in this answers here
answer1, answer2, and I couldn't find the support page for it of Google Sheets.

In layman's terms, please explain how this works.

=A5:INDEX(A2:A,COUNTA(A2:A))

i1

Values Formula
23 36
42 19
12 26
36 10
19
26
10

I was provided with this refrence to this excel's support page by the @TheMaster.

Solution

  • based on your image...

    =A5:INDEX(A2:A,COUNTA(A2:A))

    is literally translatable as

    =A5:A8

    where A5: is start of the range INDEX(A2:A is column of the range end, and COUNTA(A2:A) counts how many cells are not empty in range A2:A

    formula transcript
    =A5: =A5:
    INDEX(A2:A, A
    COUNTA(A2:A)) 8

    note that standalone =COUNTA(A2:A) results in 7 but within the formula its 8 due to INDEX being offset

    while it is short it is not reliable in case there are empty cells within A2:A range. therefore it is recommended to use:

    =A5:INDEX(A:A, MAX((A:A<>"")*ROW(A:A)))

    it's a choice of preference but the above is same as:

    =INDIRECT("A5:A"&MAX((A:A<>"")*ROW(A:A)))