Actually, if you amend that formula to this:

=IF(AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=D ata!D$3,BD1=Data!E
$3,BF1=Data!F$3,BH1<Data!G$3),EM1,0)

and copy down column FF, then all you would need to do in Data!H3 is:

=SUM(TBL_Adhoc_Supervised_UIX!FF:FF)

Hope this helps.

Pete

On Sep 4, 7:21*pm, Pete_UK wrote:
Well, of course, the file you are getting data from is itself very
large, with columns going out at least to EM or FF and with 55k or
more rows of data, and then you have the file with the formula in, and
because it is an array formula Excel will use up even more memory ...

That's why I hinted at trying a different way of doing it. Perhaps if
the formula was not in a separate file but in a separate sheet in the
GlobalReport.xls file, then you wouldn't need to specify the path or
the filename and it might work in there. You could then have the file
which currently holds your formula just lookup the values from the
other file.

And, if you did not have that condition for < then your formula could
become a SUMIF formula, as there would be only one range to check
(FF), so maybe you need to tinker about with the formula in FF1 to see
if you could produce a list of TRUE and FALSE values, something like
this:

=AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=Data !D$3,BD1=Data!E
$3,BF1=Data!F$3,BH1<Data!G$3)

This assumes that you have put the Data sheet in the same file, and
that you copy the formula down as required. Then on the Data sheet,
instead of your array formula you could just have:

=SUMIF(TBL_Adhoc_Supervised_UIX!FF:FF,TRUE,TBL_Adh oc_Supervised_UIX!
EM:EM)

With SUMIF you can use full-column references, and it will be much
quicker than the array-entered SUM(IF(...

Suppose that formula is in H3, then the values in A3 to G3 could be
fed by the original file you had and then just link back the value in
H3 as if the formula was still in that original file. And, if it was a
direct link then you wouldn't have to use INDIRECT...

So, a few more things for you to try out...

Hope this helps.

Pete

On Sep 4, 5:59*pm, Milind Keer
wrote:



Hi!!

Sorry to bother you again... this no. of records really causing a problem,
when I set 65000 as a upper limit for my array in formulas, XL is not
allowing me to save the data and giving following warning

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Is this the memory issue?? I am using 2 GHz Intel Dual processor with 2 GB
RAM.

Can you please advise on this?

Cheers!
-- Milind

"Pete_UK" wrote:
You're welcome - thanks for feeding back.

Pete

On Sep 4, 1:48 pm, Milind Keer
wrote:
Thanks again pete!! *I will certainly try to implement your suggestion.

Well!! we get this huge data file on monthly basis and currently record
contains 55000 rows which may increase in future. Hence on the safer side i
kept no. of rows 65000 which is the upper limit of XL.

I guess accurate upper limit is 65536 rows but for an array u can use 65535.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -