dynamic sum

[Masha92]

Hello all,

Hope you are safe and well!


.

I have a bit of a difficult task to do, i was hoping you can help me with.
I need to sum the cash from the cell after the condition appears "E" and dynamically onwards. And on the condition "E", the output is a zero. And then repeated again. So manually, I would put in

cell A3 (=Sum($A$3:A3)) then drag to cell C5.
Cell A6 (=0).
Cell A7(=Sum($A$7:A7)) then drag to cell C12.
Cell A13(=0)

and so forth.

Can anyone help me with one formula where i can just drag? The excel sheet is more than 8K rows.

Thank you in advance

Masha

 

[etaf]

assuming your example - is exactly how the data is laid out
then

Book14
	A	B	C
1	cash	condition	sigma
2		E
3	1		1
4	1		2
5	1		3
6	1		4
7	1		5
8	1		6
9	1	E	0
10	1		1
11	1		2
12	1		3
13	1		4
14	1	E	0
15	1		1
16	1		2
17	1		3
18	1		4
19	1		5
20	1		6
21	1		7
22	1		8
Sheet1

Cell Formulas
Range	Formula
C3:C22	C3	=IF(B3="E",0,A3+C2)

 

[Masha92]

Apologies, i was not clear. This can work if i use column C as a helper column to store the previous sum "as you did in your solution".

So the output I need, will be used in a big formula. And I do not want to use a helper column as this official report and wouldnt look nice. That is why I need a smart dynamic sum formula.

 

[etaf]

oh, Ok, not sure how to do that off hand
wheres the result output? put?

 

[Masha92]

The result output will be inside another formula like below
=((1+(F3*(E3/D3-1)))+(H3*SUM($I$3:I3)))*L3

So basically, the Sum($I$3:I3) is the manual part i am doing and want to automate it. In the sense it knows when it should fix the first part of the range and extend it till the next "E". I believe offset should do it and row but i cannot figure it out.