Hi All,

I'm in the process of converting a large Lotus 1-2-3 model into
Excel....and aside from all the other problems (that I should have no
problems resolving), this one has really got me. Hoping someone here
could help.

The problem :
The original Lotus model had the following DSUM formula.....which
obviously did not convert properly to Excel.....and which I presume
must be replaced with an Excel array formula (correct me if I'm wrong
somebody...maybe there's a better/different way to do it) ;
@DSUM($COST DATA,"ytd",TYPE=$A5#AND#DEPT=X$3)

Now, one of the sheets (in the model) has 10,000 odd rows, and I have
created 3 range names that start from row 4 and go down all the way to
row 10,000 let's say.....here's the 3 range name definitions :

xDEPT='8xx Raw Data'!$I$4:$I$10000
xTYPE='8xx Raw Data'!$I$4:$J$10000
xYTD='8xx Raw Data'!$I$4:$K$10000

Now, on a separate sheet I have the following array formula (which is
supposed to sum all the YTD numbers for a given DEPT and TYPE :)
{=SUM((xTYPE=$A5)*(xDEPT=Y$3)*xYTD)}
where $A5 contains say "Salaries", and Y$3 contains say
"Accounting"

The above array formula (correctly) gives me a (summed) amount as long
as the 3 range name definitions have the last row number less than
around 2800. If I try to increase the number to 2850 or so....I get the
#VALUE! error.

Question : is there something I can do to fix this problem...or at
least a way to get around it ?

The wierd thing is that Lotus can (easily) do it without even batting
an eyelid....however Excel just rolls over and dies (at less than the
half-way mark)

I hope the answer isn't for me to use Excel's DSUM function (b'coz
that's a b*t*h to setup)

Any ideas guys ? Really appreciate it !

Later,

C


--
xlguy
------------------------------------------------------------------------
xlguy's Profile: http://www.excelforum.com/member.php...o&userid=29666
View this thread: http://www.excelforum.com/showthread...hreadid=493783