## Explanation

It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a multi-cell array formula.

```
{=INDEX(data,{1,2,3})}
```

The results can be seen in the range D10:F10, which correctly contains 10, 15, and 20.

However, if we wrap the formula in the SUM function:

```
=SUM(INDEX(data,{1,2,3}))
```

The final result is 10, while it should be 45, even if entered as an array formula. The problem is that INDEX only returns the first item in the array to the SUM function. To force INDEX to return multiple items to SUM, you can wrap the array constant in the N and IF functions like this:

```
=SUM(INDEX(data,N(IF(1,{1,2,3}))))
```

which returns a correct result of 45. Similarly, this formula:

```
=SUM(INDEX(data,N(IF(1,{1,3,5}))))
```

correctly returns 60, the sum of 10, 20, and 30.

This obscure technique is sometimes called "dereferencing", because it stops INDEX from handling results as cell references, and subsequently dropping all but the first item in the array. Instead, INDEX delivers a full array of values to SUM in. Jeff Weir has a good explanation here on stackoverflow. Also, Excelxor has a great article here.