“Non-contiguous” range specification in formula

Question

[2018-08-01: See new material near bottom of post.]

I just ran across a Q-n-A here that included an example of a function referencing a range using a specification I had not seen before. I played around with it a little and discovered that most Excel functions will return a value when using this kind of reference:

=SUM(A1:A5:C1:C5:E1:E2:F3:F4)

Notice all the ":"s in there.

With the cursor in the formula bar, those non-contiguous cells are highlighted, as if only those cells would be totaled. However, when I experimented, I discovered that it is equivalent to this formula:

=SUM(A1:F5)

However, this formula does what I expect:

=SUM(A1:A5,C1:C5,E1:E2,F3:F4)

Example: I have the range A1:F5 defined as:

--  A   B   C   D   E   F

1   1   2   3   4   5   6

2   2   4   6   8   10  12

3   3   6   9   12  15  18

4   4   8   12  16  20  24

5   5   10  15  20  25  30 

The formula results are:

=SUM(A1:F5) is 315

=SUM(A1:A5:C1:C5:E1:E2:F3:F4) is 315

=SUM(A1:A5,C1:C5,E1:E2,F3:F4) is 117

This illustrates my point that the first two seem to be equivalent, and the third "correct" way of defining a discontiguous range gives a different result (but what I would expect).

I looked around here and elsewhere on the Internet and found no relevant discussion.

So, my questions:

1. Is that odd range reference of any real use?
2. is it really a valid sort of range reference?

---

[New material 2018-08-01]

@YowE3K wondered what would happen with an odd number of cells. Here is my example:

--  A   B   C  

1   1   1   1   

2   1   1   1   

3   1   1   1   

4   1   1   1   

Now, in a faraway cell, I enter this formula:

=SUM(A1:B3:$C$2)

Then if I drag the formula around, it creates interesting results, basically the 3-row, 2-column range extended to a rectangular range that always includes the fixed cell. The smallest sum you can ever get in this example is 3. Try it and see.

This shows a glimmer of something useful, though I haven't come up with a concrete use for it yet.

Answer 1

1. Is that odd range reference of any real use? <-- Yes. Depending on the data structure you are working on, you may not have the privilege of adding an extra row/column in the sheet.

2. is it really a valid sort of range reference? <--- Since it works, yes. Regardless of whether it is properly documented or not. congratulations of you findings though. I'm learning new things too. (: