Formula sum 10 rows, after it reset sum to 0 and sum next 10 rows

[motilulla]

Using Excel 2010
Hello,

I need a formula which can sum 10 rows, after it reset sum to 0 and sum next 10 rows
Note: Please also I need that formula work with excel version 2000.
Example…

MrExcel Question.xlsx
	A	B	C	D	E	F	G
1
2
3			Values		Sum
4			-1,00 €
5			3,00 €
6			-1,00 €
7			-1,00 €
8			3,00 €
9			3,00 €
10			-1,00 €
11			3,00 €
12			-1,00 €
13			3,00 €	10	10,00 €
14			-1,00 €
15			-1,00 €
16			3,00 €
17			-1,00 €
18			3,00 €
19			-1,00 €
20			-1,00 €
21			-1,00 €
22			3,00 €
23			-1,00 €	20	2,00 €
24			3,00 €
25			-1,00 €
26			-1,00 €
27			3,00 €
28			-1,00 €
29			-1,00 €
30			-1,00 €
31			-1,00 €
32			3,00 €
33			3,00 €	30	6,00 €
34			-1,00 €
35			3,00 €
36			3,00 €
37			3,00 €
38			-1,00 €
39			3,00 €
40			3,00 €
41			3,00 €
42			-1,00 €
43			-1,00 €	40	14,00 €
44			3,00 €
45			3,00 €
46			3,00 €
47			-1,00 €
48			3,00 €
49			-1,00 €
50			-1,00 €
51			-1,00 €
52			-1,00 €
53			3,00 €	50	10,00 €
54			3,00 €
55			3,00 €
56			-1,00 €
57			3,00 €	54	8,00 €
Sheet5

Cell Formulas
Range	Formula
E13,E53,E43,E33,E23	E13	=SUM(C4:C13)
E57	E57	=SUM(C54:C57)

Please help

Thank you all.

Regards,
Moti

 

[Joe4]

Here is one way. Place this formula in cell E13 and copy all the way down to cell E53:

=IF(MOD(ROW(),10)=3,SUM(C4:C13),"")

 

[motilulla]

Here is one way. Place this formula in cell E13 and copy all the way down to cell E53:

=IF(MOD(ROW(),10)=3,SUM(C4:C13),"")

Joe4, thank you it worked fine as per my query, I have a one more question is it possible do sum till the find value in the column “D” >0 so it will not limit 10 rows may be first sum till 1 to 5 row another 6 to12th where and till the value find in “D”?

Kind Regards,
Moti

 

[shinigamilight]

Put in E4

=IF(MOD(COUNT($C$4:C4),10)=0,SUM(OFFSET(E4,0,-2,-10,1)),"")

 

[Joe4]

Joe4, thank you it worked fine as per my query, I have a one more question is it possible do sum till the find value in the column “D” >0 so it will not limit 10 rows may be first sum till 1 to 5 row another 6 to12th where and till the value find in “D”?

Kind Regards,
Moti

I cannot think of a way to do that via formula (I think some really smart people may know of a way to do that, but I do not how).
I would probably use VBA to do something like this.

 

[motilulla]

Put in E4

=IF(MOD(COUNT($C$4:C4),10)=0,SUM(OFFSET(E4,0,-2,-10,1)),"")

Shinigamilight, applied your formula in cell E4 and copied down to cell E53 but it does not seems to work if I change the values in different cell in the column “D” even if there is non-value in column D it sum 10 in 10.

Kind Regards,
Moti

 

[Joe4]

I cannot think of a way to do that via formula (I think some really smart people may know of a way to do that, but I do not how).
I would probably use VBA to do something like this.

Are you interested in a VBA solution?
I don't want to try to develop one if it isn't something that you can use.

 

[motilulla]

Are you interested in a VBA solution?
I don't want to try to develop one if it isn't something that you can use.

Joe4, I appreciate a lot your help and offer sincerely I need a formula solution

Good Luck

Kind Regards,
Moti

 

[Fluff]

Put something in D4 & then you could use this in E4 copied down

=IF(D4="","",SUM(INDEX(C:C,LOOKUP(2,1/(D$3:D3<>""),ROW(D$4:D4))):C4))

 

[shinigamilight]

Shinigamilight, applied your formula in cell E4 and copied down to cell E53 but it does not seems to work if I change the values in different cell in the column “D” even if there is non-value in column D it sum 10 in 10.

Kind Regards,
Moti

The solution was for your original question.