Mutliple SUM with dynamic SUM range within a Dynamic Data set

[Michael.Westen]

I'm using Windows 7 with Office 2007 / Office 2010.
I need a formula (No VBA) that will be able to cater for multiple SUM functions based on the cell value in the adjacent column. This cell value remains constant (the word "Invoice") but occurs at dynamic intervals which cannot be predetermined. The SUM function must only SUM from the 1st entry after the last invoice date up to the next invoice date. This must then be applied to the entire data set.
The number of rows in the data set is dynamic and can grow or shrink.

I've added a sample sheet to try and illustrate the scenario. Dynamic Range_Multiple Dynamic Sum.xlsx

[Ace_XL]

Use in Row 2

=IF(C2="Invoice",SUM($B$1:B2)-SUM($E$1:E1),"")

and drag down

[Michael.Westen]

That will work for a rolling total. However, I want to SUM per invoice period without adding the total of the previous period to the current SUM function.
For example, the invoice amount adjacent to CELL A12 (11-Jan-12) should only SUM cells B5:B12 and the invoice amount next to CELL A16 (15-Jan-2012) should only SUM cells B13:B16.
This logic must be applied to the entire data set, bearing in mind that the "Invoice" frequencies / occurrences is not predetermined and can contain any period.

There is probably some combination formula of INDEX, MATCH, OFFSET and VLOOKUP that will solve this, but I just can seem to wrap my head around it.
Any suggestions?

[Ace_XL]

That's exactly what the formula achieves. See attached

[Michael.Westen]

I entered your formula in the wrong column previously!
Thanks, that solution will work.

[Marcol]

Maybe a bit late, but here's another idea.
Note that the formula in F2 is different from F3, Drag F3 Down
The helper column (D) can be hidden with the grouping button.