Excel formula to change sum field depending on date

I have a data table in Excel in which each row corresponds to a specific deal event and has various different fields associated in the other columns. This data looks something like:

Deal ID   Amount    Closedate   Sales person
0001      100k      1/1/2024    Person1
0002      50k       2/1/2024    Person2

In a different tab I have my report, in which each column corresponds to a different week. I use SUMIFS() formulas to sum the pipeline amount (sum of amount of all deals) on a weekly basis i.e. each column corresponds to a different week and so the deal amounts are supposed to sum based on which deals are due to close in the given week. This is also split out using various filters, e.g. pipeline value for different sales reps, attribution channels etc.

KPI             Person    01/01   08/01   15/01   22/01   29/01   05/02  12/02                              
Pipeline value  Person1   $50K    $50K    $50K    $50K    $50K    $50K   $50K
Pipeline value  Person2   $100K   $100K   $100K   $100K   $100K   $100K  $100K

The SUMIFS formula looks like:

=SUMIFS(deals_df!B:B, deals_df!C:C,"<"&'Report'!H$6, deals_df!C:C,">="&'Report'!G$6, deals_df!D:D, "Person1")

The issue is that the data table has the CURRENT deal amount (as of today), and therefore for the previous weeks, I'm not using the deal amount as of that date. The table incorrectly just has the current pipeline value showing for every week (as above). The table should look something like the below.

KPI             Person    01/01   08/01   15/01   22/01   29/01   05/02  12/02                              
Pipeline value  Person1   $10K    $10K    $20K    $40K    $20K    $20K   $50K
Pipeline value  Person2   $100K   $120K   $120K   $80K    $80K    $50K   $50K

I've been able to access the historical information and have a new data table which shows every time the field has been changed. I've used this to create a week by week table showing the deal amount value as of the end of each week (the week values align between this table and the report).

Deal ID   01/01   08/01   15/01   22/01   29/01   05/02  12/02                              
0001      $10K    $10K    $20K    $40K    $20K    $20K   $50K
0002      $100K   $120K   $120K   $80K    $80K    $50K   $50K

How can I use this to be able to sum the pipeline value as of the right week WITH the various filters applied? Im thinking it might be a SUMIFS formula with an index match of some kind. Perhaps the SUMIFS is applied to the original table with the filters, and then instead of pulling the deal amount of the table, it matches into the last table above and chooses the right amount based on the current week?

Im using this formula which correctly pulls out the deal amounts for the current week for all deal IDs, however I still dont know how to filter these deal amounts for the ones that match the filters in the original data table

=INDEX(deals_history_df!$B$2:$O$410, MATCH(deals_df!$B$2:$B$1342, deals_history_df!$A$1:$A$410, 0), MATCH('Report'!E$6, deals_history_df!$B$1:$O$1, 0))