do you have a sample workbook that you can share with enough data to help out?
Are you familiar with utilizing Level of Detail calcuations? The problem sounds like one for a LOD calculation but seeing a bit of sample data would allow the community to step help you out.
Packaged workbooks: when, why, how is a great resource for getting going with creating and sharing a packaged workbook with the community.
Let me see what I can do based on your comment and if I can provide you sample data.