SUM function produces a #VALUE! error when using in array formula

0
votes

I have the following values in a sheet (in A1:G10) range:

1   1   1   1   1   1   1
2   2   2   2   2   2   2
3   3   3   3   3   3   3
4   4   4   4   4   4   4
5   5   5   5   5   5   5
6   6   6   6   6   6   6
7   7   7   7   7   7   7
8   8   8   8   8   8   8
9   9   9   9   9   9   9
10  10  10  10  10  10  10

I need to calculate the sum of differences of values between two rows in the array above when both rows are defined by row numbers, for example: sum of differences of values between 10-th and 1-st rows, between 9-th and 2-nd rows and so on. Row numbers I defined in J1:K10 range:

1   10
2   9
3   8
4   7
5   6
6   5
7   4
8   3
9   2
10  1

Then I selected L1:L10 range, and in the first cell of it I entered the following formula:

=SUM((OFFSET(A1,INDEX(K1:K10,ROW(K1:K10)-ROW(K1)+1)-1,0,1,7)-OFFSET(A1,INDEX(J1:J10,ROW(J1:J10)-ROW(J1)+1)-1,0,1,7)))

and then pressed CTRL+SHIFT+ENTER so the formula is enterd as an array formula. And the formula returned a #VALUE! error for each cell in the range. I tried to evaluate the formula, and it evaluates correctly except the last step: SUM({9,9,9,9,9,9,9}) becomes a #VALUE!

Here is a screenshot of the sheet for clarity:

enter image description here

What I'm doing wrong? Please suggest.

1
excelexcel-formula

1 Answers

5
votes

use SUMPRODUCT in L1:

=SUMPRODUCT(INDEX($A$1:$G$10,J1,0)-INDEX($A$1:$G$10,K1,0))

and copy down.

enter image description here