Deduct amount based on sum of distinct values over a period of time...

Hello, all! First time posting. I scoured the internet thinking I could figure this out, but so far no luck.

Excuse me if my explanation is vague, due to the nature of the data, I can't disclose much... but I'll do my best.

I have a set of data (call it Ref1) that tracks 4 columns of information, each one is tracked on every row:

Time Period (in Months) - Incrementing by 1 for each row, this does not restart at 1 at the end of the year. It is ever incrementing.
Individual Record Number - This can be repeated for several lines until a new Record Number shows up.
Amount - This is a static amount that the individual on that record is paying each month.
Grouped Amount - This is an amount that the entirety of all individuals is paying per month.


Now imagine making a copy of this data (call it Ref2) and placing it on another tab.

I need a formula that will reside on Ref1 and reference the data on Ref2 and depending on if that month is the current month or in the past, deducts from the Grouped Amount the sum of the distinct individual amounts from the past up to the current month. I say distinct, because this isn't a sum of all months up to current, just a sum of only one singular month's worth for each individual.

I hope this makes sense. Any and all help is appreciated.