Sum of cell range, given Number of cell range

[ibmy]

Hi Mr Excel

What formula/vba should be in Column L & Column M ?
Given number of cell range in Column J
The data in Column H
Result in Column L is Sum while Column M is Sum of Absolute

Example : Given Number of cell range in J10 is 3. Sum the data in Column J upwardly in 3 cell range.
Result in Column L is 0 ( -0.2 + - 0.8 + 1 ) / =SUM(H8:H10) and Result in Column M is 2 ( 0.2 + 0.8 + 1 ) / =SUM(ABS(H8:H10))

Sample of 300k row data :

sample 3.xlsb
	G	H		J		L	M
1		B		No. Range		Sum	Sum ABS
2		0.1
3		0
4		0		2		0	0
5		-0.3
6		0
7		0.1
8		-0.2
9		-0.8
10		1		3		0	2
11		0.5
12		0.4
13		-0.9
14		-0.2		2		-1.1	1.1
15		0
16		0
17		1
18		0.3
19		0		3		1.3	1.3
20		0.1
Sheet2

Thanks in advance.

 

[Peter_SSs]

Try this.

ibmy.xlsm
	H		J		L	M
1	B		No. Range		Sum	Sum ABS
2	0.1
3	0
4	0		2		0	0
5	-0.3
6	0
7	0.1
8	-0.2
9	-0.8
10	1		3		0	2
11	0.5
12	0.4
13	-0.9
14	-0.2		2		-1.1	1.1
15	0
16	0
17	1
18	0.3
19	0		3		1.3	1.3
20	0.1
Sheet1

Cell Formulas
Range	Formula
L2:L20	L2	=IF(J2="","",SUM(INDEX(H:H,ROW()-J2+1):H2))
M2:M20	M2	=IF(J2="","",SUM(ABS(INDEX(H:H,ROW()-J2+1):H2)))

 

[ibmy]

Thanks @Peter_SSs
The formula working great.

In my Excel version, Sum ABS need to hit CTRL+SHIFT+ENTER to get correct result or use SUMPRODUCT ( no need to hit CTRL+SHIFT+ENTER ).
sum absolute

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
Yes, I tend to often forget about C+S+E in older versions and even if I do remember I'm not always sure which formulas need it.