INDEX has format Range, Row, Column and in the formula below the ROWS() is in the COLUMN parameter

=SUM(INDEX(Sheet1!$A$3:$E$42,,ROWS($1:1)))/SUM(Sheet1!$A$3:$E$42)

the ROWS($1:1) acts a a "counter" so in B3 it has a value of 1, in B4 a value of 2 etc so it selects the data in the first column of the INDEX range, then the second and so on.

So we select from column A, then column B .....

Hope this helps.