SUM(INDEX()) Combo

[ExcelMonkey]

I have a question about he SUM(INDEX()) combo.

I have data in a 2D array in excel. 3 rows by 2columns. Say:

1AB
245
336
497

INDEX(A1:B5,1,1) = 4
INDEX(A1:B5,1,2) = 5
However when I use 0s I get

INDEX(A1:B5,0,1) = 9
INDEX(A1:B5,0,2) = 7


And when i go, SUM(INDEX(A1:B5,0,2) ) = 18. Bascially it sums colum
2. But I don't know why. If INDEX(A1:B5,0,2) = 7, then how does th
formula know to add the entire 2nd Column

 

[Frank Kabel]

Hi
have a look at the help file For INDEX two syntax versions exist.
You're using the 'reference' version. As the help file stated: If
choosing 0 for column or row index, the entire column/row is returned.
Therefor SUM will sum the entire column in your case.
The formula
INDEX(A1:B5,0,2)
just return the first cell of this column range
Frank

 

[ExcelMonkey]

SUM(INDEX()) Combo
I have a question about he SUM(INDEX()) combo.
Reposted : Due to error in the way I presented the matrxi. Sorry

I have data in a 2D array in excel. 3 rows by 2columns. Say:

AB
45
36
97

INDEX(A1:B5,1,1) = 4
INDEX(A1:B5,1,2) = 5
However when I use 0s I get

INDEX(A1:B5,0,1) = 9
INDEX(A1:B5,0,2) = 7


And when i go, SUM(INDEX(A1:B5,0,2) ) = 18. Bascially it sums column 2
But I don't know why. If INDEX(A1:B5,0,2) = 7, then how does th
formula know to add the entire 2nd Column

 

[Roger Govier]

From Excel Help

If array has more than one row and more than one column, and only
row_num or column_num is used,

INDEX returns an array of the entire row or column in array.

 

[ExcelMonkey]

Thank