Trying to find a formulaic way to resolve the following:
A series of funds hold different % positions in a series of investments. If this were a single moment in time, it would be easy enough to aggregate a funds position across all investments to determine its overall % ownership (this is one output needed for a subsequent calculation). The challenge is that the allocation is not static with respect to time. I am looking to do a sumifs function which on any specified reference date (consider 10/1/2019 on the attached sample file) returns the value for Fund 1 (column D) corresponding to the latest allocation date for that fund <= the reference date provided. One solution would be to add data to the table for each loan on each reference date to reduce this to a simple criteria for the sumifs Where allocation date = reference date. I am hoping someone has experience with a more elegant solution.
Thank you,
Larry
Bookmarks