SUMPRODUCT with multiple conditions, wildcards, & dynamic sum range is painfully slow

[cr731]

I'm seeking some advice on how I can design my spreadsheet to be faster. I'm using a sumproduct formula similar to:

=SUMPRODUCT((ISNUMBER(SEARCH(Criteria,CriteriaRange)) * (INDEX(SumRange,,X):INDEX(SumRange,,Y))

I am using the ISNUMBER(SEARCH) approach because my Criteria could have wildcards. Sometimes I need to sum based on ABCDE and sometimes just ABC**.

Making matters worse, my sum range must be dynamic in that some rows need to sum across 1-2 columns and others 5-6.

This is because my table is like this,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer+Product[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]ABCDE-1234[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]ACD-2385[/TD]
[TD]60[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

So each row using this sumproduct must sum up a different number of months. I.e. some lines I want Jan-Mar, others Feb-Apr, and so forth (and the calendar goes for the full year).

This is absolutely painstakingly slow - this sumproduct is used in about 500 rows and then there are 3 variations on it for each row (Volume, Revenue, Margin).

My source data, where it is looking up to, is up to 50,000 rows.

I have spent HOURS on this trying to think of some way to make it more efficient but I just can't given all of the variables.

Is this just a case of trying to use Excel for something it wasn't built to do or is there something I can do to make this faster?

 

[J.Ty.]

I considered putting the dates in the rows, but here are my main question/concern is that my table already has 50,000 rows. If I put dates into the rows, I may end up with many hundreds of thousands of rows. Will the SUMIF formulas be any better under those circumstances?

Yes,definitely.

My data actually is arranged as you suggest; in that each part of the lookup string (Customer+Geography+Product) is in its own column. I've concatenated them together because I thought it would improve the speed of lookups to use only 1 field instead of 3.

As far as I understand your data model, if you use separate columns and adopt the YTD construction, you can replace SUMPRODUCT with SUMIFS. According to my experiments you might get 10 to 50 times faster execution. Because you also eliminate widcard search, which is very slow, I wouldn't be surprised by a speedup factor of 100.

Regarding the YTD suggestion... I'll have to think about that. I'm trying to think how that could be done without too much manual intervention (this is a report prepared by users that may not be so Excel savvy). Also my file is about 35 MB currently and if I duplicate my entire data set showing YTD values, I'm concerned it will grow too large to effectively distribute.

Indeed, the file will become larger, but the YTD values will be computed automatically by the simple formulas I have provided.

[

]
As a separate point, but one I need to figure out, if I do put the dates into rows, how can I, for a given Customer+Product combination, return the earliest month with a sale? I thought of,

=MIN(IF(Table[Customer+Product] = [@[Customer+Product]],Table[Date in Rows])

as an array... but (1) will that be horribly slow on a several hundred thousand row table and (2) that still doesn't address the wildcard issue?

[/QUOTE]

Read this thread: http://www.mrexcel.com/forum/excel-questions/812871-max.html, paying special attention to post #9 by Aladin Akyurek.

Best,

J.Ty.