Slow SUMIF() execution?

[ahartman]

Hi all,

I have a working solution for my problem, but it's really, painfully slow during execution. I'd like to know if there's a better solution that I've not considered.

I have a Sheet1 w/ 10 columns and 1000's of rows (usually 3000 - 7000) and data in each cell. I have a Sheet2 w/ 20 columns and 2000 rows and data filling most columns. Sheet1 contains a list of parts and Sheet2 contains a list of high priority parts to be flagged from Sheet1. Both sheets are AutoFiltered.

I want to compare each entry in column A on Sheet2 to the entire column B on Sheet1, and sum the associated quantities from column C on Sheet1 for each match into column D on Sheet2. This ends up looking like this for a single cell:

=SUMIF(Sheet1!B:B,A1,Sheet1!C:C)

This sounds exactly like the SUMIF() function, and that's what I'm using and it works correctly. But, because of the large number of comparisons, it's really slow, taking perhaps 5 minutes to fully populate all entries in column D on Sheet2. And any time I touch the workbook, i.e. to filter for a particular part on Sheet1, the recalculation time is absurd, and unnecessary.

My workaround has been to let the calculation run the first time, then disable recalculation, copy the values from column D on Sheet 2 into a new Sheet3, then deleting Sheet2 and re-enabling calculations. Since I'm never changing the data, I don't mind losing the calculation for those few steps, but I have to wonder that this all seems like a terrible hack and that I must be missing a more elegant (and speedy!) solution.

Any advice or tips would be appreciated. If I'm already doing about as well as I can, then advice on using VBA to disable or enable calculation would be appreciated.

Thanks,
Adam

[DonkeyOte]

If you create a back up version and delete only the SUMIF functions does the performance issue resolve itself or not ?

I'd be very surprised if SUMIF proved to the be the culprit - it is pretty efficient (utilising only the used range of range referenced).

You mentioned filters which will inevitably be playing a part ... are you using any SUMPRODUCTs in conjunction with SUBTOTAL/OFFSET in your file ?

[ahartman]

If you create a back up version and delete only the SUMIF functions does the performance issue resolve itself or not ?

I'd be very surprised if SUMIF proved to the be the culprit - it is pretty efficient (utilising only the used range of range referenced).

You mentioned filters which will inevitably be playing a part ... are you using any SUMPRODUCTs in conjunction with SUBTOTAL/OFFSET in your file ?

I do have some LOOKUPs, SEARCHes, and other COUNTIFs sprinkled throughout the workbook, and when I strip out all that stuff, the SUMIF goes fast as lightning! So, I'll just have to reorder the data manipulations to put this operation first and it should be good to go. Thanks for the insight!

-Adam

[DonkeyOte]

Re-reading your post the implication is that once the SUMIF functions are removed c/o your hack then the file is ok... that does seem odd I confess (filtering can have bad performance effects though IMO)

Given you have 1:many relationship between Sheet2!A and Sheet1!B your options are perhaps a little more restricted than normal (or more of a faff at any rate).
That said... sorting your data on Sheet1 per Col B would allow you to use Binary Search on Sheet2 to find the appropriate instances of the appropriate value (per Col A) thereby referencing far fewer records.
With the references identified you can conduct a basic SUM in Col D (c/o INDEX range) rather than SUMIF.

I put together a quick test file with 7k source and 2k summary (can't attach given today's events) but I can state that having done some testing on my laptop (4GB Ram admittedly) I got the following results:

2k SUMIF with 7k unsorted data took around 2.00 seconds to calculate

With the same volume of data SUM(INDEX:INDEX) utilising 2 MATCHes per SUM and with sorted source data took around 0.40 seconds to calculate

Little gain in the big scheme of things...

[DonkeyOte]

posts crossed -- I can't edit posts so new reply -- if / when you can post a sample file it might be worth doing so.

I confess I'm surprised that SUMIF alone should have such an impact ... filtering can often do nasty things calculation wise given altering row visibility is a volatile action however SUMIF is not volatile so should not be affected... that said I confess I don't know enough re: the model and calc engine in general to make any definitive assertions I'm afraid - not without seeing it in action.

[ahartman]

posts crossed -- I can't edit posts so new reply -- if / when you can post a sample file it might be worth doing so.

I confess I'm surprised that SUMIF alone should have such an impact ... filtering can often do nasty things calculation wise given altering row visibility is a volatile action however SUMIF is not volatile so should not be affected... that said I confess I don't know enough re: the model and calc engine in general to make any definitive assertions I'm afraid - not without seeing it in action.

Making a sanitized file wouldn't be entirely easy, especially since it appears that the undesirable slowness happens when all my manipulations are present together. To re-write the VBA which generates the sheets based on randomized dummy data would be pretty time consuming. I'll keep the idea on my back-burner list, and if I ever master time travel, I'll post something for you to look at.

Again, thanks for your help and advice.

-Adam

[DonkeyOte]

Last edit on my part...

...when I strip out all that stuff, the SUMIF goes fast as lightning!

I just noticed per the above that removing everything but the SUMIFs resolved the SUMIF performance issue which is a relief as that would have been my expectation

I had previously misread the above (as being the opposite) hence my surprise and latter posts - all misplaced - apologies for unnecessary confusion caused.

As a general rule ... if you have lots of Volatile functions and/or a few Volatile Arrays / Sumproducts then models utilising filtering are susceptible to adverse performance impact (running on Auto Calc).
LOOKUP*/SEARCH/COUNTIF etc shouldn't really have hideous effects in their own right (*pending 2/1 type approach etc). Traditionally it's the Arrays and SUMPRODUCTs that are the killers given the way they are processed behind the scenes.