Search code examples
google-sheetssumspreadsheet

Partial sum with SUM and INDEX

As you can see in the screenshot bellow I have wrote the following formula to calculate the partial sum from row 3 to 11:

=SUM(INDEX(A1:A15,E1):INDEX(A1:A15, E2))

It works but I can't explain why? It seems like I have wrote =SUM(3:11) which should return 14, but somehow it understands that I want the range not between 3 and 11 numbers, but elements in column A that go from row 3 to row 11. I can't understand this as in the manual of INDEX we read:

INDEX Returns the content of a cell, specified by row and column offset.

and this means that it returns not a reference on the cell but its content - its value. How can we explain that?

enter image description here

Solution

  • INDEX changes its behaviour depending on the context in which it is used. Because you have used the INDEX(...):INDEX(...) construction (i.e. either side of the range operator (:), this is interpreted to mean that you wish each INDEX to refer to a cell reference (as this is the only context in which the range operator has any meaning).

    So your formula in this case resolves to =SUM(A3:A11).