Formula Set Up Challenge

Excel Forum,

Thank you for your time reading this post. I am grateful for your help.

I am struggling to find an efficient way to set up a formula that will achieve my goal.
I have a series of cash flows timed by quarter. I need a formula that will add up my cash flows each quarter and once a unit sells it is no longer included in the cash flow stream retroactively.
Attached is a spreadsheet with an example of what I am trying to accomplish.

For example, in quarter #2 (row 18) no units sold so I just sum the cash flows. In quarter #7 (row23), 2 units sell and I need to add up all of my quarterly cash flows again except I need to exclude the units that sold. In other words, the cell labeled "sale" needs to trigger additional arithmetic.

Thank you for your time and guidance.

Hi,

I ended up with quite long formula (partialy because your input and outbut are in a sense rotated/transposed), but it seems to work. In E17 and copy down and right:

=IF(E$15>$D17,"",E$14-SUMPRODUCT(E$5:E$13,(OFFSET($D$5:$D$13,0,MATCH($D17,$E$15:$O$15,0))="SALE")+(OFFSET($D$5:$D$13,0,MATCH($D17,$E$15:$O$15,0))="")))

if it really works (please check) - you could add IFERROR at the beginning to avoid #N/A! in rows starting 31-03-2012
wher you have no data in columns on input side.