where “data” is the named range B5:B10. 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: 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: which returns a correct result of 45. Similarly, this formula: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
