How can you sum the total dollar value between two dates with dynamic rows and columns? The objective is to find the dollar value associated with a given name (row) during the week ending in the date column.
There are two tabs: SOURCE and CALCULATION. SOURCE is a table of names as the Y axis and dates as the X axis with dollar values as the content. CALCULATION is a table of the same names in the Y axis and Week Ending dates in the X axis (i.e., where SOURCE may have 1/1, 1/2, and 1/3, CALCULATION will only show 1/7 as the end of the week).
Some things to note:
1. Not all Week Ending dates appear in the SOURCE table and not all dates in the SOURCE table appear in the CALCULATION table
2. The dates (columns) are not in chronological order. (This is a copy-paste values from a pivot table that pulls from another table. I don't know why the column dates are not in order though the source data is...)
3. Some dates appear in the future so the formula cannot be dependent on TODAY()
A scrubbed version of the material below is here: Book1.xlsx
Thank you!
Bookmarks