dynamic sum

Masha92

New Member
Joined
Jan 27, 2019
Messages
48
Hello all,

Hope you are safe and well!


.
1614352181504.png




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
 

Attachments

  • 1614352050803.png
    1614352050803.png
    11.1 KB · Views: 3

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,958
Office Version
  1. 365
Platform
  1. MacOS
assuming your example - is exactly how the data is laid out
then
Book14
ABC
1cashconditionsigma
2E
311
412
513
614
715
816
91E0
1011
1112
1213
1314
141E0
1511
1612
1713
1814
1915
2016
2117
2218
Sheet1
Cell Formulas
RangeFormula
C3:C22C3=IF(B3="E",0,A3+C2)
 

Masha92

New Member
Joined
Jan 27, 2019
Messages
48
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,958
Office Version
  1. 365
Platform
  1. MacOS
oh, Ok, not sure how to do that off hand
wheres the result output? put?
 

Masha92

New Member
Joined
Jan 27, 2019
Messages
48
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.
 

Forum statistics

Threads
1,143,677
Messages
5,720,260
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top