Dynamic Sumifs for ALL criteria

[mikeTRON]

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:

Please Login or Register  to view this content.

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

[mikeTRON]

Apparently my computer went crazy and double posted without attaching a file.

(I am still trying to get this laptop to attach the file.)


The Dashboard in question is on the Dashboard tab. If you have any questins, let me know.


Also, to give a little bit of background information. I basically have too much data and have to pull it into PowerPivot first, then aggregate and get it all lined up to be pasted into this template. The more recent version of the template has a few macros to automatically apply new named ranges to ONLY capture the number of rows used on the DATA tab and then I am working on trying to use pivot tables as a single column for the data validation instead of static lists, that way they are also automatically updated.

If you have any advice on any of that, feel free to let me know.

Thanks!

[mikeTRON]

I think the file was too large to attach, so I removed most of the Sample data and it is attached below.

Reporting Template (sampleDATA).xlsb

[mikeTRON]

I know this is a fairly vague question, but does anyone have any advice or suggestions?

[martindwilson]

well you can get rid of the indirect with
=SUMIFS(INDEX(DATA!$K:$DI,,MATCH(D$4,DATA!$K$5:$DI$5,0)),Scenario,$E$2,FC_Product,$C7,Group,$A$1,Division,$A$2,UniqueWkbk,$A$3,FC_CustCH,$A$4,FC_Cust,$A$5)

[mikeTRON]

Hmm, so will the index+match be faster than the indirect?

Also, on my actual file, I have two dynamic reports like this, the one is attached previosuly, and the other just allows 17 columns of the indirect formulas so we can show Jan-Dec, Q1-Q4 and Full Year.

When I change a drop down on ONE tab, will it force a recalculation on BOTH tabs? If so, then the index+match might be more efficient right?

[martindwilson]

well index match is apparently non volatile so should be quicker but its the sumifs thats probably slowing things down, do you really think you are going to use all 1,048,576 rows? reduce the ranges to the maximum you are likely to need
you could try dynamic ranges based on the length of column a eg
=b6:INDEX(b:b,COUNTA(A:A)+5)
so
Gals201208
would be

=R6:INDEX(R:R,COUNTA(A:A)+5)

[mikeTRON]

Hey Martin,

I tried the index+match method (slick answer by the way) but it was slower than my volatile Sumifs+Indirect function, possibly because it forces excel to look through the entire column of data.

So this is a template to be used by the managers needing the data, so I have a process that will dump the data in appropriately then I have a macro that will fire off when the data set changes. The macro basically loops through all of the used columns to make a static named range to capture only the used rows.

At it's largest the dataset is about 400k rows and there is NO way of shrinking it any further as I have already done so from 2M rows.


I think now I am leaning towards learning how to create a macro (ActiveX) controlled pivot table that will allow for filtering on the dataset using the exact same options now (time frame, region, division, area, customer, and section etc) that way I can do all of the work with the pivot table (including the aggregation) and simply use index+match to grab the correct data.

Does this seem like a decent solution?