Sum if criteria by grouping

Hello!

I need help with automating my home budget calculations.
I would like to be able to use just a single formula, as opposed to relying on extra columns, if that is at all possible.
Attached is an Excel-document with sample data and comments to explain what I'm trying to achieve.


The problem:
I want to sum values until a group of values meet a certain criteria.

Since this is a houshold budget, I want to evaluate groups (AKA periods) of data, not single records.


Explanation:
I have a set budget of 100 every period for fuel for my car.
Now, imagine that I at the start of every period put this money in a bucket.
Whenever I need to gas up the car, I use the money available in the bucket.
If I don't spend all of this period's money, the remainder is left in the bucket and can be used in upcoming periods.
If I need to spend more than the budgeted amount, I will use any remainder from previous periods.
When the bucket goes empty, it is empty.

The final line is the problem I'm having. In real life, if I need to spend more money than I have, I borrow from someone else. On the start of the next period, the bucket is still empty. But Excel-formulas will carry the debt with it from the previous period which results in a negative value that is forever part of the budget.


Examples:
Periods:
excel-periods.png
As you can see, I'm not using calendar months but custom date ranges. I'm including them because the period, or "group of spendings", will have to be looked up in the final formula.

Spendings:
excel-spendings.png
These are records of every time I used money from the bucket to pay for fuel.

Expected results:
excel-expected.png
These are the values I'm trying to achieve.
The red cell with value 330 for period 6 is exhausting the period budget as well as all of the remainders from previous periods, resulting in a negative value -30 (see the debug column). The green cell with value 100 for period 7 is my special logic - I want to reset the remainders if the previous period was negative.

Debug columns:
excel-debug.png
These are values, step-by-step, that explain different situations.
For period 6, you can see that the "outgoing bonus" is -30, because it is the sum of incomes minus expenses.
But as seen in expected results, I want the following periods to not include the values if the previous period was negative.


What I have tried:
SUMIFS
It works only if I ignore my resetting-logic. I can't figure out a way to make it evaluate the sum of a period instead of the value of each record.

SUMPRODUCT and DSUM
I have experimented, but not come up with anything to show because I can't figure out how to do the grouping-logic.


What I would like:
I would like to have a single formula that I can apply to different categories (it is not only fuel, as in this example, but around twenty more categories I would like to evaluate).
The logic should be something along the lines:

"Add the sum of previous periods's remainders until the sum of the previous periods's remainders is negative".

It's harder than I imagined to try and put it in writing, but I hope you get what I mean.
If period 6 is negative, I want period 7 to have 0 incoming bonus, and period 8 to have period 7's remainder as incoming bonus (ignoring anything before period 6) and so on.

Hi. There's an English expression: "robbing Peter to pay Paul". If you need to pay off one debt, you have to find the money from somewhere. In your case, you need a column to show that money HAS HAD to come from elsewhere in the system. I created such a column.

G5:
=IF(F5="","",SUMIF(Spendings[Period],F5,Spendings[Spent]))

H5:
=IF(F5="","",F5*$H$1-SUM(G$4:G4)+SUM(I$4:I4))

I5:
=IFERROR(IF(F4="","",MAX(H5,100)-H5),"")

In SE, you may need ; instead of ,