Dynamic Sumifs for ALL criteria

So I am playing with a few ideas to try and help the performance of a particular dynamic report that consists of named ranges, indirect (and apparently you cant have dynamic named ranged in an INDIRECT formula, which was unexpected) then applying 6 filter criteria.

Here is an example of a formula:

=SUMIFS(INDIRECT(D$4),Scenario,$E$2,FC_Product,$C7,Group,$A$1,Division,$A$2,UniqueWkbk,$A$3,FC_CustCH,$A$4,FC_Cust,$A$5)

This formula allows the user to control everything with data validation through drop downs. The search criteria are linked to a drop down, and the drop down has all the possible choices AND and "ALL" possibility, which just chooses "*" and renders that portion of the SUMIFS useless. This works but slows down when I apply the entire data set of 500,000 to 1,000,000 rows. Basically do any of you have any ways I can make this process more effecient?

I originally thought about having nested IF formulas but then realized that nests and formula would be huge and I am not completly sure if that would help anything because of the volatile INDIRECT portion of the formula. I also tried to see if I could come up with any clever solution using choose, because from what I understand it only calculates the chosen formula.


Can anyone offer any advice?

I am thinking it might be time for me to spend some more time in the VBA world and use active-X controls to control pivot tables to allow some much less complex formulas to grab the data from the pivot table.

I have attached an example of the Workbook I am talking abou