if you want to create a "variable" address
let's say the "lookup" range varies between 10000 to 40000 rows

AND you have many sumproduct type lookups...
your best bet for a "performance" formula:

use a cell with a formula that returns
the "correct" address of the rangetocheck

then in your sumproducts use indirect to get the range.
you lose some by using indirect.
you win some by having your formula countif!
working on the correct nr of rows.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


do you really need to check the 65k rows?

if you can make that range smaller,
performance will definitely improve.






--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


John Contact wrote :

Thanks seems I missed the obvious sumproduct function to replace my
arrays

i.e.
=SUM(IF(DUDListData!$T$2:$T$65536=DUDListData!$Y$1 ,1,0))/COUNTIF(DUD
Li stData!$A$2:$A$65536,"1")

"JE McGimpsey" wrote:

Almost certainly not. Just the overhead involved in a VB call
generally makes UDF unattractive for functions that can be
performed on the worksheet.

Take a look here for tips on speeding things up:

http://www.decisionmodels.com/optspeed.htm



In article ,
John Contact wrote:

Hi,

I have a number of ormulas that are performing rather slowly,
calculation of the workbook takes approximately 2 minutes. My
question is, if they were re written to calculate in vb would
this speed things up?

Thanks in advance

John