How do you return summarized dynamic array from Filter function

[papa_austin]

Hello Excel Community,

I am working to improve an excel based financial model. 

It takes inputs from a large table with multiple filter criteria inputs from the user. 

And then the calculation is executed from a DataTable (going through multiple inputs at once).

Below is an example of what my input looks like and what I am looking to produce.

Example Data SetUser Inputs and Desired Results

• I am looking to improve my current formula  

  • Adding up 5 Filter functions (one filter formula for each outcome column)

  • Having a formula for every year (filter for by particular year)

  • Repeat this for every year and every row i need to export .... (i have like 30 rows)

    that is 5*60*30 = 9000 filter functions (1800 excel formulas)

I am looking to see if there are ways to improve this by...

1. One formula per row (instead of one per year - so it will need to be dynamic array)

I know I can use a Pivot table (and using VBA solution to update filters based on user selection) but updating Pivot filters does not work with a DataTable.  I can't trigger VBA (that updates Pivot filter) every scenario that DataTable executes. I am open to fix that problem; it could be entirely possible that i am using event handlers incorrectly. 

I don't have Lambda yet. This feels far more doable with Lambda .... 

[#2]

@papa_austin 

Is it possible to provide sample file with information as on screenshots ?

[#3]

Hello @Sergei Baklan Sergei,
I just attached the sample file in the post.
It's important to note that I have to calculate multiple-input combinations via DataTable.

[#4]

@Sergei Baklan 

So, I have been trying to solve this by using a Pivot table unsuccessfully. 

1. Setup - There are two files involved; file#1) one with data, file#2) calculation workbook
2. File #1 has a master table of data and a Pivot Table with filters
3. File #2 has a DataTable that runs through a multiple possible filter combination 
   1. Filter combinations are user inputs in cells 
4. Let's say my DataTable is set up to run 5 different filter combinations.
   1. When I calculate the DataTable, I would like to have the Pivot Table filters update 5 times and result in 5 different calculation values
   2. I use Worksheet_calculate()  event handler to update the pilvot table filters 
   3. But I cannot get worksheet calculate() to be triggered 5 times when the DataTable is calculated. 
   4. I know if I press F9 five times (manually go through the DataTable), it works just fine. 

[#5]

@papa_austin 

As I understood the question is not about PivotTable but how to do that with dynamic arrays. I missed with sample file - it's totally different from what is on screenshot. What is Data Table in it, how filter will be defined and what shall be desired result if we consider that file?