custom function instead of sumproduct

[dorabajji]

Hi,
I am using sumproduct formula
=sumproduct(--(d3=sheet2!d:d),(e:e))
This slows my sheet..
i know that this is volatile formula and used sum if.. but i cannot open the another workbook and keep.. sheet2 reference is from another workbook..
SO i want a macro to lookup and sum all the instances..

[TMS]

It's slow because you are using full column references. Restrict it to a more specific range ... maybe a 1000 rows?

[dorabajji]

Yes, but i dont know when the source workbook will be updated.. so only i gave full reference.. as i dont know the last row( as the source file will be updated every now and then)

[Rick Rothstein]

Yes, but i dont know when the source workbook will be updated.. so only i gave full reference.. as i dont know the last row( as the source file will be updated every now and then)

Do you have an idea of what the maximum number of rows will ever be (1000, 5000, 10000, or whatever)? All you have to do is choose a range that should never be exceeded by the data, ever, but that is significantly smaller than the full column reference.

[dorabajji]

Do you have an idea of what the maximum number of rows will ever be (1000, 5000, 10000, or whatever)? All you have to do is choose a range that should never be exceeded by the data, ever, but that is significantly smaller than the full column reference.

Hi Rick,
At present it is 700.. each month average entries is around 250 to 400.. that workbook is updated by sales staff. I have to report the summary from this workbook to manager. So, I don't have this source file .. only the formula file, I will be having.. source file will be in d network folder.

[TMS]

400 x 12 = 4800. So, if you allowed 10,000 that should be more than twice what you need. To be on the (very) safe side, you could allow 20,000 or even 50,000 ... still way better than over a million rows.

It will still be safe and way better performance.

[Rick Rothstein]

TMS has posted the answer where I was going with my question. My calculation was going to be 400 * 12 (months) * 10 * (years) which would be 48,000 so I was going to recommend 50,000 (TMS's upper limit)... that would cover you for about 10 years which I'm guessing should be more than enough. If you don't think you need to cover that much into the future, you might consider using a range of 25,000 which would cover 5 years worth of updates instead.

[Larry.LeBlanc@O]

Can you put the sales data in a “formal table“ so that the column being referenced will automatically update as new data are appended?