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))