Originally Posted by

**hrlngrv**
A2:A99 is the smallest single-area range containing A2 and A99.

More rigorously, it is the rectangular range with A2 and A99 at its upper-left and lower-right corners. Since they are in the same column we have an edge case but the terminology still applies.

More general example,

**C5:X7:G3:P9** is the smallest single-area range containing

**C9**,

**X7**,

**G3** and

**P9**, which happens to be the same range as C3:X9.

I was surprised to find that this syntax is even legal. **[EDIT]**However, this seems to be correct. Here is a SUM formula using that range. I have put a border around C3:X9. You will see that the numbers at the corners of that range are included in the sum.

If you select the formula, you will see that Excel has interpreted the expression to be two separate rectangular ranges, which are not the same as C3:X9. **[EDIT]** I cannot account for this, or why it contradicts how the sum is calculated.

Where it gets odd is generalized references into worksheets other than the one containing the formula using such references, for example, This!X99 with the formula =SUM(INDEX(Other!A$1:A$100,foo):Other!$A$100).

I do not understand what point you are making with this last example. It doesn't seem odd to me at all.

INDEX returns a range (assuming **foo** is a valid integer), and so the overall expression inside SUM is a range starting from some cell within the range Other!A$1:A$100 and ending at the cell Other!$A$100 (If the sheets in the two ranges are not the same sheet you will get a VALUE error.)

## Bookmarks